Change Field Format in Query

[LB79]

Hi All,

I'm having a format problem I'm hoping someone can advise me on.
I have a query (Q1) that, among other things format a date field as YYYYMM (Field1).
I have a second query (Q2) whose data source is Q1.
In Q2 I need to link Q1 to a table but Field1 is being reformatted as text (confirmed by running a make table query).
I want Field1 to be Number format to eliminate a mismatch error.
I know how to change the format of a field in a table using VBA but I cant seem to find a way of changing the format within a query.
Is it possible? Is there a thread I havent found?

Many thanks

 

[namliam]

when you lookup the format function in the access help it will tell you it returns a string value.... thus it will always return a text field.

If you want to make your field1 a numeric field, instead of Format(x, "YYYYMM") simply wrap that in CInt(Format()) or CDouble
Alternative way might be, Year(x) * 100 + month(x), since both functions return numbers you can use them in math like this
Year * 100 will make 201300 and add the month to it for 201309

Either way should make your field be a numeric field , no VBA needed

 

[LB79]

Thats great - Thank you