I have inherited a db with a table with year in one field and month in another. There is no day field. I have a need to use the DateSerial function so that I can allow a query to gather data for the past 24 months based upon an entered date. To accomplish this, I have tried to create a new field in a query that is concatenated in the form of Month & "/01" & "/" & Year. I attempted to format the field as mm/dd/yyyy but the format doesn't appear to be working. Is this even possible? Is there a better way of accomplishing this than DateSerial?

It works great if I have put a field in the table that is a shortdate. However, due to other issues and the table covering 10 years with at least 100 vendors per month, I would prefer not to do that.

Just a quick check:

Are your fields named MONTH and YEAR?

If so, rename them to avoid the internal conflicts as they are reserved words.

My example below uses the Month and Year fields renamed in your table as dMonth and dYear. You should avoid reserved words and symbols in your field names as it can cause things that would normally work to go screwy.

Then, something like this should work in your query:

MyDate:CDate(dMonth & "/01/" & dYear)

See if that might not work.

BL
hth