Web Development



Need help with Access query

By ccornett79 ·
I have a table with 2 fields that I need to compare in my query. Setup like this:

Field 1 - Plan ID
Field 2 - Received Date
Field 3 - Filing Limit Date

Fields 2 and 3 are not Date/Time format. They are Number. Ex: 1100527 would be 5/27/2010.

I need the query to only give me records where Field 2 is greater than or equal to Field 3, for a specific plan number.

Example: For plan id 999321, I need it to only show me records where the Received date is greater than the filing limit date of 3/30/2009 (or 1090330).

I have been unsuccessful in doing this. I was getting a data type mismatch error, so I changed them all to Text, then attempted Date/Time format, and none of them work. It will only pull in all records for this certain Plan ID, or will pull in no records for the plan.

I'm still feeling my way through learning access. Any help is appreciated. Thanks!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

If 1100527 is 27/05/2010 then

by Tony Hopkinson In reply to Need help with Access que ...

Convert('20' + Substring(Convert(VarChar(9),Field2),2,6),DateTime) >= Convert('20' + Substring(Convert(VarChar(9),Field3),2,6),DateTime)

would be the sql.

Probably Cdate and Mid$ in access functions

Basically convert ?YYMMDD to string, chop the first digit off prefix with century to get a universal date format YYYYMMDD and then convert to a date for the compare.

Related Discussions

Related Forums