Take data from a number of fields & make it format a certain way in another field.

**Mariostg** · Sep 22 '10, 06:41 PM

If you have dozens of security, each with their own format, you are up for a good challenge... :)
I make the assumption that they are all saved in the same format but displayed and reported into their own flavor...
That sounds like a VBA approach maybe using a Select Case statement. There is probably a requirement to have a table containing the security with its own format that can be used as a template.

**OldBirdman** · Sep 22 '10, 07:41 PM

No solution here, but a some comments:

1) This can be done with a Select Case but if a new Security is added, or one decides to change something, it will require programmer / database designer intervention at some time in the future. This program will never be finished.
2) Templates - Have each Security define their method and write VBA routine to handle. Access (Microsoft) does this with date formatting where 'yy' means 2 digit year, and 'yyyy' means 4 digit year.
This does not require a programmer in the future, UNLESS somebody wants some feature not in the patterns.
3) All of this means using dates in non-date format, making programming more difficult, and therefore more likely to have errors (bugs).
4) Entering month twice, once as ExpM and again as Month will generate errors when they don't agree. The 3 character name should be derived from ExpM.
5) ExpM, ExpD, and ExpY should be derived from a standard date format. Otherwise, dates such as 2/29/2010 or 9/31/11 will be entered and accepted
6) Without a standard date, sorting and testing of the dates becomes very cumbersome.

I would tell the Boss to form a committee and work out a single method that will be acceptable to all. Otherwise he is bypassing Access' logic and effectively requesting custom programming. Such programming becomes expensive over time.

**Fspinelli** · Sep 23 '10, 03:24 PM

Thank you Mariostg and OldBirdMan!

I see. what I will do is get rid of that three charachter month. As long I can take the month from the expiraton date (format that date as mm/dd/yyyy)and make it output a three charachter month for one of the securities output flavor (format requirements) then I should be ok. So I will now have to learn how to write that out in VB.

We use the same handful of securities. It is rare that we would add any, but if we had to I want to be able to make it so and as simply as possible.

Would I then create a table for each security as a template so that when one is used it (for lack of better words) tallies up the securities format in a cell in the row that has the other data (the cells where the data is entered and are the ingrediants for the final cells output?

I hope I'm phrasing this technically close!

**Mariostg** · Sep 23 '10, 03:39 PM

As OldBirdMan said, try to minimize the number of format you have to use. Consistency is important.
Now that whatever string format you try to get, I am not sure if you want to store its end result in a table. i.e. record SPX 101017C00975000 in a field. If so, I don't think it is really advantageous because you would only duplicate information in a different field that exists somewhere. So just create that string when you report or query.

If you have lots of security patterns, you probably want to store them in a table, otherewise I would probably hard code them maybe in an array. But whatever mean you choose, Select Case will likely be involved. Hard coding is prabably easier to get you started.

**Fspinelli** · Sep 24 '10, 12:41 PM

Concatenate! That's the word I was searching for in an effort to explain.

Thank you for the information everyone! I am learing so much from here.

Now I just have to figure out how I can take my formulas in Excel and make them work in Access (or at least figure out how to translate it.)

Here's something; what if I have my expiration date in a format without the forward slash ( / ) in it? It's easy to add them to code, but find it hard to remove if a format does not want the slash. Anyway, I have the date like this: mmddyyyy. No slashes.

What if I have a format that takes ddmm (something in between like C or P) and then yyyy? How do I make it so Access knows to take certain characters and arrange them a certain way?

I'm trying to concatenate it but I'm not getting anywhere with it.

**Mariostg** · Sep 24 '10, 12:49 PM

To remove your slashes, or any character, use Replace function:

Code:

stripDate = replace("2010/06/02","/","")

This will replace the / with nothing.

Abuot the C and P thingnie, can you be a little more explicit. Better exemple maybe. You know, it is Friday...

**Fspinelli** · Sep 24 '10, 01:18 PM

Thankfully it is Friday.

Ok, I know how to concatenate simple things like first and last names, stuff like that, but this is a doozy.

I have 5 fields in a table.

[Security] - it's a combobox with list values of names of securities. There's about 6 of them.

[Underlying] - a combobox with list values, currently only has "SPX" in it, but eventually we may want to add another.

[PutCall] That's the P & C Thingy. I have a combobox with list values: P;C. The end user would pick one from the drop down box.

[Strike] It's a field in a long integer number format that is used to enter a quantity (like we bought 775 options of something).

[Expiration] That's the slash thing - but thanks for the information! Learned something else new today!

I have to take the information entered into each field of this table and make it produce a Symbol in another field of one table. And the field[Security] is the key.

Each [Security] has a way they arrange their data into a symbol.

For instance
Security A arranges the data like this:
SPX 8/09 C775

[Underlying] A combobox where the SPX came from
<space>
[Expiration] mmddyy is what I currently have but Security A only needs d/yy (that's where the 8/09 comes from).
<space>
[PutCall] combo box allowing user to choose if it's P (for Put) or C (for call)
[Strike] That's the quantity (number), using 775 for this examples

I have a data entry screen where the 5 fields are and the user enters the information. With a push of a button they want it so that the format the Security chosen should be in pops up.

Here are a few examples a few securities and their desired format:

SPX 08/09 C775

SPX US 08/22/09 C775

SPX 090822C00775000
yep...OMG! yy first, month, day, and then the C thingy and zeros for padding!)

SPXAUG775C2009
(yep! another OMG! Month is spelled out in 3 characters,and a full year at the end.)

I've received some very good advise but was hoping someone might have another suggestion to try.

Trying to concatenate the five fields and have it populate into one field named "Symbol" (in same table or another table, whatever is proper).

**OldBirdman** · Sep 24 '10, 02:40 PM

I would create a function to build the string. Function arguments would be all variables mentioned in above posts that are necessary for this. I'm not sure whether "SPX" is a constant (probably not).
I have started a function to demonstrate what I have in mind. It DOES NOT have all the information needed, nor is it complete even for the information provided above. It is meant to be a template.
I tried it in immediate window with these results:

Code:

?buildsymbol("A",123,12,7,30,"C")
SPX   120730C00123000

Here is the code:

Code:

ublic Function BuildSymbol( _
    strSecurity As String, _
    lngStrike As Long, _
    intExpY As Integer, _
    intExpM As Integer, _
    intExpD As Integer, _
    strCP As String) _
    As String

 Dim strResult As String
    
    Select Case strSecurity
    Case "A"
        strResult = "SPX   "
        strResult = strResult & Format(intExpY, "00")
        strResult = strResult & Format(intExpM, "00")
        strResult = strResult & Format(intExpD, "00")
        strResult = strResult & strCP
        strResult = strResult & Left("00" & lngStrike & "000000", 8)
        '....
    Case "B"
        strResult = "SPX "
        strResult = strResult & Format(intExpM, "00")
        strResult = strResult & "/" & Format(intExpD, "00")
        '....
    Case "C"
        '....
    Case Else
        MsgBox "New security department - Contact programmer for help"
        Exit Function
    End Select

    BuildSymbol = strResult
End Function

Once I had this working, I would replace all these date parts with 1 date field. The function could find the month, day, and year using Access built-in funcions such as

Code:

intMonth = DatePart("m", dteExpDate)

where dteExpDate is passed to the function instead of intExpY, intExpD, and intExpM.

**Fspinelli** · Sep 28 '10, 02:41 PM

Learning to how to ask the question - arranging string

Yes, you are all right - I'm making a mountain out of a mole hill. After everyone's advise and further reading I think I know how to explain what I have to do - here it is in detail.

Objective is to make a "symbol" out of the information entered into a data table.

I have three tables:

# 1) Data Table (where the information is entered and stored)

# 2) Expiration Date Table (where the expiration dates reside and are used as a combobox in the Data Table)

# 3) Symbol Table (where the information from the data table gets arranged into a special format to create a special symbol, and stored.)

From all that a report is generated.

In the data table (# 1) I have the following fields:

[Strike] which is a quantity
[Underlyer] combobox with "SPX" as the default
[PutCall] combobox with a "C" and a "P"..."C" is default.
[Expiration] a combobox with a list of expiration dates. The combobox is made from the Expiration Date Table (#2). Whatever the end user choses needs to resides in the [Expiration] field

From those fields I need to create a symbol. There are several symbol format requirements. I don't know how to create a query that takes the info from the data table and lay it out in the several types of symbol formats. Let's say for giggles we need to buy a quantity of 775 shares of something. <space> means I need a space in between characters.

In my symbol table each type needs to spit out a format.

Type 1: The format has to be:
[Underlyer] <space> M/YY <space> [PutCall][Strike]

symbol Example: SPX 8/09 C775

Type 2: The format has to be
[Underlyer]<space> MM/DD/YY <space> [PutCall] [Strike}

symbol example: SPX 08/14/09 C775

To add to the head banging I need:
Type 3 [Underlyer] <space> yymmdd [PutCall] <here I have to put a couple of zeros>[Strike]<some more zeros>

symbol example: SPX 090822C00775000

More head banging:
Type 4 I have to make the format:
symbol example: SPXAUG775C2009

So I am baffled at the type of query I have to write to get the symbols to spit out in the various types of formats. How to pick the first three letters of the month, or only use one character of the month, or add those zeros.

Is there hope for me?

**Mariostg** · Sep 28 '10, 03:17 PM

Hi Fspinelli.
There is hope, and I don't think there is a need to explain further what you want to achieve. OldBirdMan gave you quite a good option if you look again at what he wrote. All you need is as many Select Case as Type of security you have...

Also, if you are unfamiliar with the format function, you should read about it. Example, to get the first three letters of the month and capitalize them you woud do :

Code:

x = UCase(Format("2010-08-04", "mmm"))

**Fspinelli** · Sep 28 '10, 03:33 PM

Select Case and the way you used the UCase, I will research that. Thank you.

**Fspinelli** · Sep 30 '10, 08:42 PM

It's wonderful! Thank you all for your help!!

Take data from a number of fields & make it format a certain way in another field.

Take data from a number of fields & make it format a certain way in another field.

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment