Incorrect Year in query

[steve1111]

Hello,

i have a query that pulls a read-only SQL table, i have check in that table and the date field is set up as a Date/Time type, however the db was set up with the date and time in the same field. (1/1/2014 9:00:00 AM)

I have set up the SQL code below to pull out just the Date and not the time. however when i enter my criteria on the form, only the correct month and days load, it pulls in all other years with that month/day. Any help would be appreciated.

my Access level - on the lower end of medium experienced.

SELECT IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," "))) AS [Date], IIf(InStr([dbo_Rides]![ApptDatetime]," "),Mid([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")+1)) AS [Time]
FROM dbo_Rides
WHERE (((IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")))) Between [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtFromDate] And [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtToDate]));

 

[CJ_London]

Look at the error of your ways - You are converting a date field into a text field by using the left function and then trying to see if it is between two dates which are numeric- that is the crux of your problem.

A date field is stored numerically as a decimal. The value to the left of the decimal point represents the date and to the right, the time which is calculated as a percentage of 24 hours (so 12 noon would be 0.5, 3am would be 0.125, 3:30am would be 0.145 etc)

So suggest you use the datevalue function instead - something like

WHERE DateValue([dbo_Rides]![ApptDatetime]) Between [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtFromDate] And [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtToDate]));

 

[steve1111]

The DateValue worked great, thanks so much!