Converting concactenated fields to date

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

Guest

Hello -

I am having trouble with this expression in my query:

ExpiryDate: CDate(Format([ccExpMonth] & "/" & ([CCExpYear]+2000,"Short
Date")))

I am trying to find Expiry dates less than 90 days from today using criteria

<Now()+90

ccExpMonth and ccExpYear are both integers and could be one to 2 digits

Thanks!
sandy
 
The problem is you don't have a complete date. You don't have a day.

You can use the first of the month as the expiration date
ExpiryDate: DateSerial(ccExpYear,ccExpMonth,1)

or the last of the month
ExpiryDate: DateSerial(ccExpYear,ccExpMonth+1,0)

Or the 15th day of the month
ExpiryDate: DateSerial(ccExpYear,ccExpMonth,15)

Then you can put criteria against that expression
Criteria: <DateAdd("d",90,Date())


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi John -

this worked perfectly for getting the date into the correct format, but when
I add the criteria

<snip> AND
((DateSerial([ccExpYear],[ccExpMonth],1))<DateAdd("d",90,Date())));


I get a type mismatch.

Did I miss something?

sandy


John Spencer said:
The problem is you don't have a complete date. You don't have a day.

You can use the first of the month as the expiration date
ExpiryDate: DateSerial(ccExpYear,ccExpMonth,1)

or the last of the month
ExpiryDate: DateSerial(ccExpYear,ccExpMonth+1,0)

Or the 15th day of the month
ExpiryDate: DateSerial(ccExpYear,ccExpMonth,15)

Then you can put criteria against that expression
Criteria: <DateAdd("d",90,Date())


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sandy said:
Hello -

I am having trouble with this expression in my query:

ExpiryDate: CDate(Format([ccExpMonth] & "/" & ([CCExpYear]+2000,"Short
Date")))

I am trying to find Expiry dates less than 90 days from today using
criteria

<Now()+90

ccExpMonth and ccExpYear are both integers and could be one to 2 digits

Thanks!
sandy
Click to expand...
Click to expand...
 
One more thing -

When I run the query with no criteria, the date shows up properly

CCExpMonth 11.00
CCExpYear 9.00
ExpDate 11/1/2009

but when I try to sort the data set, I get a "Data Type Mismatch in
Criteria" error.

I just noticed the decimals in the CC fields when I pasted them here. I went
back to look at the table and confirmed that these fields are defined as
Integer, Fixed, with 0 decimal places... why would the .00 be showing?

Thanks again,
sandy
 
Are all of your fields populated or do you have some Nulls in your ccExp...
fields?

--
Duane Hookom
Microsoft Access MVP


Sandy said:
One more thing -

When I run the query with no criteria, the date shows up properly

CCExpMonth 11.00
CCExpYear 9.00
ExpDate 11/1/2009

but when I try to sort the data set, I get a "Data Type Mismatch in
Criteria" error.

I just noticed the decimals in the CC fields when I pasted them here. I went
back to look at the table and confirmed that these fields are defined as
Integer, Fixed, with 0 decimal places... why would the .00 be showing?

Thanks again,
sandy


Sandy said:
Hi John -

this worked perfectly for getting the date into the correct format, but when
I add the criteria

<snip> AND
((DateSerial([ccExpYear],[ccExpMonth],1))<DateAdd("d",90,Date())));


I get a type mismatch.

Did I miss something?

sandy
Click to expand...
Click to expand...
 
Yes there were! I added IS NOT NULL to those 2 fields and now the query works
perfectly.

many thanks to you both!
Sandy


Duane Hookom said:
Are all of your fields populated or do you have some Nulls in your ccExp...
fields?

--
Duane Hookom
Microsoft Access MVP


Sandy said:
One more thing -

When I run the query with no criteria, the date shows up properly

CCExpMonth 11.00
CCExpYear 9.00
ExpDate 11/1/2009

but when I try to sort the data set, I get a "Data Type Mismatch in
Criteria" error.

I just noticed the decimals in the CC fields when I pasted them here. I went
back to look at the table and confirmed that these fields are defined as
Integer, Fixed, with 0 decimal places... why would the .00 be showing?

Thanks again,
sandy


Sandy said:
Hi John -

this worked perfectly for getting the date into the correct format, but when
I add the criteria

<snip> AND
((DateSerial([ccExpYear],[ccExpMonth],1))<DateAdd("d",90,Date())));


I get a type mismatch.

Did I miss something?

sandy
Click to expand...
Click to expand...
Click to expand...
 
Any chance some of the field values are null for ccExpYear and
ccExpMonth? If so, that can cause problems and give this type of
message. ANd if the two fields are text fields you could have bad data
(non-number characters) in them which could also lead to problems.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John -

this worked perfectly for getting the date into the correct format, but when
I add the criteria

<snip> AND
((DateSerial([ccExpYear],[ccExpMonth],1))<DateAdd("d",90,Date())));


I get a type mismatch.

Did I miss something?

sandy


John Spencer said:
The problem is you don't have a complete date. You don't have a day.

You can use the first of the month as the expiration date
ExpiryDate: DateSerial(ccExpYear,ccExpMonth,1)

or the last of the month
ExpiryDate: DateSerial(ccExpYear,ccExpMonth+1,0)

Or the 15th day of the month
ExpiryDate: DateSerial(ccExpYear,ccExpMonth,15)

Then you can put criteria against that expression
Criteria: <DateAdd("d",90,Date())


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sandy said:
Hello -

I am having trouble with this expression in my query:

ExpiryDate: CDate(Format([ccExpMonth] & "/" & ([CCExpYear]+2000,"Short
Date")))

I am trying to find Expiry dates less than 90 days from today using
criteria

<Now()+90

ccExpMonth and ccExpYear are both integers and could be one to 2 digits

Thanks!
sandy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top