about searching Dates within a range using quries or SQL

[hempknight]

Hi! all

this is probably been answered in part elsewhere but does anyone know how to get Dates that are 30 days from the current date on the system?

my Database has a field to hold the Warranty End Date for a computer system.

the user can either Enter "N/A" or a Date in this format dd/mm/yy anything else and it should popup a msgbox indicating a problem.

to allow the use of N/A and the "/" in the Date i set it to text field and even when i use the DateValue and DateDiff functions to try and convert the Date from text to a number and then compare to now using Datediff i get all sorts of different errors including Data type mismatch

i've played around with DatePart and some other functions i found as well..to no avail.

so if a field has a date in it and not "N/A" then it needs to search which are 30 days from expiry.

i know i'll have to construct a seperate search to filter all the "N/A"s out of the records being passed to this particular search but just wondering how to do this bit and whether i can do both in one search or patch of SQL..

Thanks!

[Rabbit]

ExpDate: iif(DateField = "N/A", "", CDate(DateField))
Criteria = BETWEEN Date() AND DateAdd("d", 30, Date())
 

[hempknight]

ExpDate: iif(DateField = "N/A", "", CDate(DateField))
Criteria = BETWEEN Date() AND DateAdd("d", 30, Date())
 

thanks for that Rabbit!

i'll chuck that in and give it a try..

BTW: iif ?? is that meant to have 2 i's? or that a typo?

[Rabbit]

iif is meant to have two i's, it's supposed to mean "if and only if".

[hempknight]

oops! forgot to ask..Rabbit umm does the text in line 1 for the data validation rule (like how only NA or a date can be entered) or is it part of the text i should chuck into my Warrenty End Date query??

sorry i know i said i'd chuck it in and see if it works..but i only got a chance to access the school's Dbase just today and i've been flat out prepping for Uni Exams..so i forgot where i was meant to place it..

[Rabbit]

The two lines are what you would put in the query. The second line is the criteria for the calculated field.

[hempknight]

Sorry to be such a pain in the bum Rabbit..

but i did as you said and even when i puposely modified a comp to expire on the 1/9/07

the query didnt show anything

here's the code in SQL format since i cant screendump from the school's comp for some reason.. man i really should have gone back and re-do all my access course this is making me feel so stupid right now..~_~

SELECT ComputerDatabase.[Primary ID], ComputerDatabase.[Computer Name], ComputerDatabase.[Asset number], ComputerDatabase.[Serial number], ComputerDatabase.[Computer Model], ComputerDatabase.[Port number], ComputerDatabase.Location, ComputerDatabase.[Warranty End Date], ComputerDatabase.[Computer Usage type], ComputerDatabase.[Misc info]
FROM ComputerDatabase
WHERE (((IIf([Warranty End Date]="N/A","",CDate([Warranty End Date]))) Between Date() And DateAdd("d",30,Date())));
 

BTW: the query gave me an "invalid use of Null" error until i when thru the dbase and manually filled in every comp that didnt have a warranty end date with "N/A". after that it ran..but doesnt show anything.. >_<

[Rabbit]

Try putting the CDate around the iif statement instead.