Converting to dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two fields in a table (the table is linked ODBC so I can't change the
properties). The month and year fields are formatted as numbers. I need to
convert them to dates in the format of mm/yyyy. I have done the following:

format(([month]&"/"&[year]),"mm/yyyy"). That combined them but when I built
a query calculating what the date was 6 months ago and 12 months ago and do a
between statement, I get an error stating that there is a data type mismatch.
Can anyone help please? Thanks in advance.
 
Stacey said:
I have two fields in a table (the table is linked ODBC so I can't
change the properties). The month and year fields are formatted as
numbers. I need to convert them to dates in the format of mm/yyyy.
I have done the following:

format(([month]&"/"&[year]),"mm/yyyy"). That combined them but when
I built a query calculating what the date was 6 months ago and 12
months ago and do a between statement, I get an error stating that
there is a data type mismatch. Can anyone help please? Thanks in
advance.
Click to expand...

A date needs a day but aI believe it will default to (1) if you don't supply
one. However; the Format() function returns a string, not a date. Try...

DateSerial([YearField], [MonthField], 1)
 
Hi Stacey,

Try use "CDate" function that will convert the date string into an date/time
type.
CDate([month] &"/" & [year])
 
Thank you,
I tried that and I still get a data mismatch. What I ended up doing is
using datevalue in front of my formula and creating a make table. It made it
into a date/time format and seems to be working. THanks again.

Rick Brandt said:
Stacey said:
I have two fields in a table (the table is linked ODBC so I can't
change the properties). The month and year fields are formatted as
numbers. I need to convert them to dates in the format of mm/yyyy.
I have done the following:

format(([month]&"/"&[year]),"mm/yyyy"). That combined them but when
I built a query calculating what the date was 6 months ago and 12
months ago and do a between statement, I get an error stating that
there is a data type mismatch. Can anyone help please? Thanks in
advance.
Click to expand...

A date needs a day but aI believe it will default to (1) if you don't supply
one. However; the Format() function returns a string, not a date. Try...

DateSerial([YearField], [MonthField], 1)
Click to expand...
 
Stacey,

<<I tried that and I still get a data mismatch.>>
You have to think about it! That's what Help is for.

Rick gave you the means to extract a valid date from the two fields. Once
you have that, what you then have to do is use the DateAdd() function to
calculate the date 6 months and 12 months prior, then extract the individual
numbers representing the month and year, using the Month() and Year()
functions respectively.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Stacey said:
Thank you,
I tried that and I still get a data mismatch. What I ended up doing is
using datevalue in front of my formula and creating a make table. It made
it
into a date/time format and seems to be working. THanks again.

Rick Brandt said:
Stacey said:
I have two fields in a table (the table is linked ODBC so I can't
change the properties). The month and year fields are formatted as
numbers. I need to convert them to dates in the format of mm/yyyy.
I have done the following:

format(([month]&"/"&[year]),"mm/yyyy"). That combined them but when
I built a query calculating what the date was 6 months ago and 12
months ago and do a between statement, I get an error stating that
there is a data type mismatch. Can anyone help please? Thanks in
advance.
Click to expand...

A date needs a day but aI believe it will default to (1) if you don't
supply
one. However; the Format() function returns a string, not a date.
Try...

DateSerial([YearField], [MonthField], 1)
Click to expand...
Click to expand...
 
Thank you

JL said:
Hi Stacey,

Try use "CDate" function that will convert the date string into an date/time
type.
CDate([month] &"/" & [year])

Stacey said:
I have two fields in a table (the table is linked ODBC so I can't change the
properties). The month and year fields are formatted as numbers. I need to
convert them to dates in the format of mm/yyyy. I have done the following:

format(([month]&"/"&[year]),"mm/yyyy"). That combined them but when I built
a query calculating what the date was 6 months ago and 12 months ago and do a
between statement, I get an error stating that there is a data type mismatch.
Can anyone help please? Thanks in advance.
Click to expand...
Click to expand...
 
Did it work???


Stacey said:
Thank you

JL said:
Hi Stacey,

Try use "CDate" function that will convert the date string into an date/time
type.
CDate([month] &"/" & [year])

Stacey said:
I have two fields in a table (the table is linked ODBC so I can't change the
properties). The month and year fields are formatted as numbers. I need to
convert them to dates in the format of mm/yyyy. I have done the following:

format(([month]&"/"&[year]),"mm/yyyy"). That combined them but when I built
a query calculating what the date was 6 months ago and 12 months ago and do a
between statement, I get an error stating that there is a data type mismatch.
Can anyone help please? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top