query expression in a form

[cstraim]

Hey all

I am trying to add an expression in a form to work like this:

I have three fields, two are fields that the user enters information
to, and the last one is a field that concatenates the two fields.

The fields are like this

City - User Edit
Account ID - User Edit
Account # - Concatenate City and Account ID

So for example
City - CHI
Account ID 1234
Account # CHI-1234

I already have the SQL expression I use in queries to build the account
number field

Account #:Switch(Len([Account ID])=2,[City] & "-" & "0000" & [Account
ID],Len([Account ID])=3,[City] & "-" & "000" & [Account
ID],Len([Account ID])=4,[City] & "-" & "00" & [Account ID],Len([Account
ID])=5,[City] & "-" & "0" & [Account ID],Len([Account ID])=6,[City] &
"-" & [Account ID])

1) Should I be using this expresssion in a Form
2) If so, where should I put it in the text fields property section?

Or is this not as simple as that

Thanks for your help

Cory

 

[Jeff L]

It looks to me like you are trying to make the AccountID be six digits,
correct? This would be much easier: City & "-" & Format(AccountID,
"000000")

You can easily do this on a form. Create a textbox and in the box put
an = in front of the line I gave you above.

Hope that helps!

 

[cstraim]

Thanks Jeff!!!!

It looks to me like you are trying to make the AccountID be six digits,
correct? This would be much easier: City & "-" & Format(AccountID,
"000000")

You can easily do this on a form. Create a textbox and in the box put
an = in front of the line I gave you above.

Hope that helps!

Hey all

I am trying to add an expression in a form to work like this:

I have three fields, two are fields that the user enters information
to, and the last one is a field that concatenates the two fields.

The fields are like this

City - User Edit
Account ID - User Edit
Account # - Concatenate City and Account ID

So for example
City - CHI
Account ID 1234
Account # CHI-1234

I already have the SQL expression I use in queries to build the account
number field

Account #:Switch(Len([Account ID])=2,[City] & "-" & "0000" & [Account
ID],Len([Account ID])=3,[City] & "-" & "000" & [Account
ID],Len([Account ID])=4,[City] & "-" & "00" & [Account ID],Len([Account
ID])=5,[City] & "-" & "0" & [Account ID],Len([Account ID])=6,[City] &
"-" & [Account ID])

1) Should I be using this expresssion in a Form
2) If so, where should I put it in the text fields property section?

Or is this not as simple as that

Thanks for your help

Cory