table formatting not carrying to calculated field in query

  • Thread starter Thread starter scottyboyb
  • Start date Start date
S

scottyboyb

Greetings,

I have a table with the field "State" in it. The field size is set to 2
charactors in length and Format is set to ">". It works fine in the table.

I have a query based only on that table. The "State" field in the query
observes the format. But I have a simple calculated field that concantates
the City,State & Zip to one field. It does not observe the table formatting.

CSZ: ([City] & ", " & [StateOrProvince] & " " & [PostalCode])

Table: State field = NY displayed
Query: State field = NY displayed
CSZ: Westfield, ny 14787

I tried: CSZ: ([City] & ", " & Format([StateOrProvince], >) & " " &
[PostalCode]) but I get an error message that the expression's syntax is
wrong.

What am I missing?

Thanks,
Scott
 
If you really want to store upper case then I would use the after udpate
event of the control on your form to change the entered value to
UCase([StateOrProvince]). If you don't do this, you need to use something
like:
CSZ: ([City] & ", " & UCase([StateOrProvince]) & " " & [PostalCode])
 
Greetings,

I have a table with the field "State" in it. The field size is set to 2
charactors in length and Format is set to ">". It works fine in the table.

I have a query based only on that table. The "State" field in the query
observes the format. But I have a simple calculated field that concantates
the City,State & Zip to one field. It does not observe the table formatting.

CSZ: ([City] & ", " & [StateOrProvince] & " " & [PostalCode])

Table: State field = NY displayed
Query: State field = NY displayed
CSZ: Westfield, ny 14787

I tried: CSZ: ([City] & ", " & Format([StateOrProvince], >) & " " &
[PostalCode]) but I get an error message that the expression's syntax is
wrong.

What am I missing?

Thanks,
Scott
Click to expand...

The Format property of a field just controls how that field is displayed - not
what's stored, and it isn't inherited by any other use of the field.

I'd suggest running an update query updating State to UCase([State]) in your
table, and putting code in the data entry form to uppercase the field as it's
entered. You could also use

CSZ: ([City] & ", " & UCase([StateOrProvince]) & " " & [PostalCode])

but this would be inefficient since it must call the function for every
record.
 
Thank you. I understand your reasoning.

If I understand correctly, Format in table design only controls display.
That's good. But why does the query see the formatting in the field "State"
and not see it in the concantated field? And because the concantated field
does not see it, the form field that uses the concantated field does not see
it. Jeesh, talk about uninteneded consequences.

So, what is the preferred method of dealing with this? What I would like is
to have in the final product, in this case the form (based on query
concantated field), display the following:
CSZ: Westfield, NY 14787
insted of
CSZ: Westfield, ny 14787

Is there anything in this:
CSZ: ([City] & ", " & UCase([StateOrProvince]) & " " & [PostalCode])
I need to change? Or what suggestions do you have?

Many thanks,
Scott


John W. Vinson said:
Greetings,

I have a table with the field "State" in it. The field size is set to 2
charactors in length and Format is set to ">". It works fine in the table.

I have a query based only on that table. The "State" field in the query
observes the format. But I have a simple calculated field that concantates
the City,State & Zip to one field. It does not observe the table formatting.

CSZ: ([City] & ", " & [StateOrProvince] & " " & [PostalCode])

Table: State field = NY displayed
Query: State field = NY displayed
CSZ: Westfield, ny 14787

I tried: CSZ: ([City] & ", " & Format([StateOrProvince], >) & " " &
[PostalCode]) but I get an error message that the expression's syntax is
wrong.

What am I missing?

Thanks,
Scott
Click to expand...

The Format property of a field just controls how that field is displayed - not
what's stored, and it isn't inherited by any other use of the field.

I'd suggest running an update query updating State to UCase([State]) in your
table, and putting code in the data entry form to uppercase the field as it's
entered. You could also use

CSZ: ([City] & ", " & UCase([StateOrProvince]) & " " & [PostalCode])

but this would be inefficient since it must call the function for every
record.
Click to expand...
 
I have been having some peculiar behaviour with the post function of the
forums and I accidentally posted this twice. The other post produced exactly
what I needed for the problem and these answers gave me more knowledge to
keep me from asking this again.

Many thanks to all,
Scott

scottyboyb said:
Thank you. I understand your reasoning.

If I understand correctly, Format in table design only controls display.
That's good. But why does the query see the formatting in the field "State"
and not see it in the concantated field? And because the concantated field
does not see it, the form field that uses the concantated field does not see
it. Jeesh, talk about uninteneded consequences.

So, what is the preferred method of dealing with this? What I would like is
to have in the final product, in this case the form (based on query
concantated field), display the following:
CSZ: Westfield, NY 14787
insted of
CSZ: Westfield, ny 14787

Is there anything in this:
CSZ: ([City] & ", " & UCase([StateOrProvince]) & " " & [PostalCode])
I need to change? Or what suggestions do you have?

Many thanks,
Scott


John W. Vinson said:
Greetings,

I have a table with the field "State" in it. The field size is set to 2
charactors in length and Format is set to ">". It works fine in the table.

I have a query based only on that table. The "State" field in the query
observes the format. But I have a simple calculated field that concantates
the City,State & Zip to one field. It does not observe the table formatting.

CSZ: ([City] & ", " & [StateOrProvince] & " " & [PostalCode])

Table: State field = NY displayed
Query: State field = NY displayed
CSZ: Westfield, ny 14787

I tried: CSZ: ([City] & ", " & Format([StateOrProvince], >) & " " &
[PostalCode]) but I get an error message that the expression's syntax is
wrong.

What am I missing?

Thanks,
Scott
Click to expand...

The Format property of a field just controls how that field is displayed - not
what's stored, and it isn't inherited by any other use of the field.

I'd suggest running an update query updating State to UCase([State]) in your
table, and putting code in the data entry form to uppercase the field as it's
entered. You could also use

CSZ: ([City] & ", " & UCase([StateOrProvince]) & " " & [PostalCode])

but this would be inefficient since it must call the function for every
record.
Click to expand...
Click to expand...
 
Back
Top