date value query grid problem

[pb21]

I have the following field which is stored as a string: [field3]"2006090111123500"

the first 8 characters represent the date but backwards.

I want to filter between dates.

I have the following in the query grid:

CallDate: DateValue(Right(Left([Field3],8),2) & "/" & Mid(Left([Field3],8),5,2) & "/" & Left([Field3],4))

with criteria: >=#01/10/2006# And <=#31/10/2006#
This seems to work on october data no problem

I had hoped to put all the september data into the same data table and filter but it failed so I created a new table called september data and pointed the query to it but for september:
with criteria: >=#01/09/2006# And <=#30/09/2006#

however the query fails if I take out the calldate expression it works no problem.

In fact it works providing I dont try and filter on date or sort by ascending.

it says data type mismatch in criteria expression. I had hoped that datevalue would turn the string into a date.

Its strange that it works in one query and not in the other with only the data being different.


Can anyone see why this should fail have I done something stupid?

kind regards in advance.

peter

 

[neileg]

I would have thought that CDate() is the function you need.

I wonder if you are having a UK/US date problem. This can bite you when you least expect it.

 

[pb21]

Hi I tried Cdate (mystring) but that just returns error in the query column

 

[neileg]

Well it works fine for me using Datevalue and cdate and with mixed October and September data!

 

[pb21]

Hi I believe you with this:

Expr1: CDate((Right(Left([Field3],8),2) & "/" & Mid(Left([Field3],8),5,2) & "/" & Left([Field3],4)))

it returns no problem.

as soon as I do this:

>=#01/09/2006# And <=#30/09/2006#

it returns the error.

To avoid the scenario how do I force it to ignore the us/uk issue?

its a strange problem

 

[pb21]

Here is the where part of sql string complete thats causing the issue:

WHERE (((CDate((Right(Left([Field3],8),2) & "/" & Mid(Left([Field3],8),5,2) & "/" & Left([Field3],4))))>=#9/1/2006# And (CDate((Right(Left([Field3],8),2) & "/" & Mid(Left([Field3],8),5,2) & "/" & Left([Field3],4))))<=#9/30/2006#));

access has flipped the dates

how do I stop this occurring?

 

[pb21]

Hi Guys well I discovered the problem (although the date issue is still interesting)
one row existed in the data that did not have data so the query was working and then bombing out part way through.

thank you so much for your help

regards

 

[neileg]

Great!

By the way, it is usually preferable to use Between rather than <= and >=

 

[raskew]

Hi -

You need to start using dates as Access stores them. Take a look at this MSKB article http://support.microsoft.com/kb/q130514/ to see how Access stores dates/times.

Add a Date/Time datatype field to your table, e.g. MyDate, then use an update query to convert your string to an actual date, e.g. from the debug (immediate) window:

x = "2006090111123500"
y = dateserial(left(x,4), mid(x,5,2), mid(x,7,2))
? y
9/1/06
'to show that it's actually stored as a date
? cdbl(y)
 38961

Once done, start using normal built-in date/time functions to resolve your issues.

Bob

 

[pb21]

I just used your more elegant method of using dateserial that is fantastic resolution thank u to all