Converting concactenated fields to date

[Sandy]

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

 

[John Spencer]

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]

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

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
..

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

 

[Sandy]

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

 

[Duane Hookom]

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

--
Duane Hookom
Microsoft Access MVP

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

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

 

[Sandy]

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

many thanks to you both!
Sandy

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

--
Duane Hookom
Microsoft Access MVP

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

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]

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

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
..

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