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

[Fspinelli]

Me again!

I am not sure how to phrase this, "but", I have to create a field that produces data in a specific format from other fields of data.

Creating the table and the query was the easy part...now taking the data that reside in that table, adding in a new field that takes the info from the other fields and makes it look a special way in that new field.

I'm not sure where to put the code in the query. If it's [this security] then the layout in that new field is [their format].

I have a data entry screen that asks for:
Strike (the number)
ExpM (expiration month)
ExpD ("day)
ExpY ("year) (I did them this way because of the format requirements below.)
Month (drop down, chose a three character month, saves to table)
Underlying (same thing which is the SPX I'm talking about)
Call/Put with is a drop down, choose C or P
Security drop down with choices

A silly example:
Security A likes their symbol to look like:
SPX 101017C00975000
(three spaces after the SPX)
SPX YYMMDD(C or P) some zero padding, the number, more zero padding

Here's another exmaple:
Security B likes their symbol to look like:
SPX 10/10/09C975
(one space after the SPX)
SPX MM/DD/YY(C, number and no padding)

And then finally another:
Security C likes their symbol to look like:
SPXOCT975C2010
SPX(three chara month),number,C,full year. No spaces for anyone here.

Now my screen has fields for data entry and a few have a drop down (list value) combo box. Whatever they choose is written to the main table. Anyway, upon clicking "Enter" button I would like it to enter and save the record into the main table but also pull the info entered in the other fields into the new field that requires it to show in its special format.

I'm sure I'm confusing the heck out of everyone so I'm ready for the questions to begin!

[Mariostg]

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]

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]

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]

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]

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]

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

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]

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]

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:

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

Here is the 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

intMonth = DatePart("m", dteExpDate)
 

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

[Fspinelli]

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]

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 :

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

[Fspinelli]

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

[Fspinelli]

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