If statements criteria

[BED]

Hello all, the is my first post!!!!

I have an issue with getting a query to work based on a form.

Heres the issue:
Im pulling data history and i was to have an option of the number of history records to pull. This is based on a "Date()-"X".

On my form i have 5 text boxes. I want to insert the day number in the box, and have that translate to the criteria, however if i only want to input 3 numbers i want it to pull only 3. If i put in 4 numbers i want it to pull those 4 dates.

Here is my code.

Date()-[Forms]![Find_Cos/Inv_Errors]![Text_Day1] Or 
Date()-[Forms]![Find_Cos/Inv_Errors]![Text_Day2] Or 
Date()-[Forms]![Find_Cos/Inv_Errors]![Text_Day3] Or 
Date()-[Forms]![Find_Cos/Inv_Errors]![Text_Day4] Or 
Date()-[Forms]![Find_Cos/Inv_Errors]![Text_Day5]

I keep getting ODBC failures which means im inputting something wrong.



What do you guys think

[jzwp11]

I'm not sure what you are trying to accomplish. Using date()-x implies that you are selecting a date x days prior to today. The number of records returned depends on how many records have that date.

First, I would use the dateadd() function rather than the -x, dateadd("d",-X, date()). Using your form reference then, the function is as follows:

dateadd("d",-1*forms![Find_cos/Inv_Errors]![text_day1],date())

Wouldn't it be easier for your user to just use 2 date fields and the BETWEEN..AND operator in the WHERE clause of the query:

SELECT....
FROM...
WHERE yourdatefield BETWEEN forms![Find_cos/Inv_Errors]![text_day1] AND forms![Find_cos/Inv_Errors]![text_day2]

[BED]

I have no idea how it worked so flawlessly when i changed it to a date add function, but it did. Thank you very much sir, i appreciate you saving my computer from a hard impact with my fist =)

[jzwp11]

You're welcome.

I have seen some strange issues when doing a straight arithmetic operation on a date field & the dateadd() function usually takes care of the problem. There are several other date related functions that come in handy as well including the datediff(), datepart(), dateserial() and weekday() functions.

[BED]

nice, i have copied those down and am going to look into them. With the work i do i use a lot of different date searching criteria, and i only used Date()- function.....until you help. Thanks again.