asked on

Help building query - query error - Data type mismatch in criteria expression

I'm using Access 2000 and I'm running into problems with a Query.

Here is what I have from the SQL view:

SELECT DateValue(2000+Mid([Field1],8,2) & "/" & Mid([Field1],2,2) & "/" & Mid([Field1],5,2))+TimeValue(Mid([Field1],11,8)) AS DateTimeStop, Left([Field3],Len([Field3])-1) AS IPAddress, Val(Right([Field6],Len([Field6])-1)) AS Seconds, 24 AS Stream
FROM 9072
WHERE ((([9072].Field5)="closed"));

What I'd like to do is to further restrict the query to records where the Seconds column has a value of 10 or greater. When I do this, I get "Data type mismatch in criteria expression."

Steve

nico5038

Try

SELECT DateValue(2000+Mid([Field1],8,2) & "/" & Mid([Field1],2,2) & "/" & Mid([Field1],5,2))+TimeValue(Mid([Field1],11,8)) AS DateTimeStop, Left([Field3],Len([Field3])-1) AS IPAddress, Val(Right([Field6],Len([Field6])-1)) AS Seconds, 24 AS Stream
FROM 9072
WHERE ((([9072].Field5)="closed")) and Val(Right([Field6],Len([Field6])-1)) >= 10 ;

Nico)

steveca

ASKER

Yes, that's what I had tried first, but I get the error with that.

thenelson

Is [Field6] a date field? To get seconds:
Datepart("s", [Field6]) AS Seconds

Val(Right([Field6],Len([Field6])-1)) will not work
Access stores all dates as doubles as the number of days since 12/30/1899 12:00:00 AM (Format(0,"mm/dd/yyyy hh:nn:ss AM/PM") = 12/30/1899 12:00:00 AM). For example 09/11/2005 10:56:40 AM is stored as 38606.4560185185 or 38606.4560185185 days since 12/30/1899 12:00:00 AM (CDbl(#09/11/2005 10:56:40 AM#) = 38606.4560185185). You can input and display the date/time in any format you choose.

nico5038

Strange as the VAL() should return a numeric result.
1) Sure the error is from this field
2) How does the field6 data look
3) Is field6 always filled ?
If not, try:
SELECT DateValue(2000+Mid([Field1],8,2) & "/" & Mid([Field1],2,2) & "/" & Mid([Field1],5,2))+TimeValue(Mid([Field1],11,8)) AS DateTimeStop, Left([Field3],Len([Field3])-1) AS IPAddress, Val(nz(Right([Field6],Len([Field6])-1))) AS Seconds, 24 AS Stream
FROM 9072
WHERE ((([9072].Field5)="closed")) and Val(nz(Right([Field6],Len([Field6])-1))) >= 10 ;

Nic;o)

thenelson

It would be helpful to use meaningful names for your fields and table. This will help you too when you come back in a year to make changes.

steveca

ASKER

The original data is being imported from a log file. [Field6] contains data that looks something like this:

345)
67)
22456)
0)
4)
265)

The reason for the code above is to convert the text strings to numbers and to remove the ")" from the end.

As my code above stands, it works fine to get my data into columns. I just can't get the query to limit itself to records with more 10 or more seconds recorded.

nico5038

Hmm, then try:

SELECT DateValue(2000+Mid([Field1],8,2) & "/" & Mid([Field1],2,2) & "/" & Mid([Field1],5,2))+TimeValue(Mid([Field1],11,8)) AS DateTimeStop, Left([Field3],Len([Field3])-1) AS IPAddress, Val(nz([Field6])) AS Seconds, 24 AS Stream
FROM 9072
WHERE ((([9072].Field5)="closed")) and Val(nz([Field6])) >= 10 ;

The Val() will ignore the trailing ")".

Nic;o)

thenelson

Do you got the error with?
SELECT DateValue(2000+Mid([Field1],8,2) & "/" & Mid([Field1],2,2) & "/" & Mid([Field1],5,2))+TimeValue(Mid([Field1],11,8)) AS DateTimeStop, Left([Field3],Len([Field3])-1) AS IPAddress, Val(nz(Right([Field6],Len([Field6])-1))) AS Seconds, 24 AS Stream
FROM 9072
WHERE Val(nz(Right([Field6],Len([Field6])-1))) >= 10 ;

steveca

ASKER

Nico,

The query as I've posted it does not cause an error. It is only when I try to further limit the query to only include records where the value of Seconds is 10 or greater that I get an error.

As the query currently stands above, the Seconds column displays a number for every record. The number is right justified when it displays, which indicates to me that it is a number data type. When I remove the Val(), the number is left justified.

The Field6 data is text. See my previous post for samples of the data.

There is always data for both Field6 and Seconds.

Steve

nico5038

Hmm, tried a table with:
Field6      ValValue
67)      67
11)      11
12)      12
0)      0
11)      11
12)      12
123)      123
1)      1
0)      0
Also the comparison with "WHERE (((Val(NZ([Field6])))>10));" works flawlessly.

Just right-click the field in the graphical query editor and select the "Properties" for the field, check or there's no format defined there.

Nic;o)

steveca

ASKER

thenelson,

Thanks for you advice about meaningful names...

Guess what? They are! 9072 refers to a very specific log file. The field numbers directly relate to this log file. You will also note that I have named my query columns.

I do see where it would appear that my names are meaningless though. So thanks for caring...

Steve

nico5038

Hmm, numbers in names can give trouble, try:

SELECT DateValue(2000+Mid([Field1],8,2) & "/" & Mid([Field1],2,2) & "/" & Mid([Field1],5,2))+TimeValue(Mid([Field1],11,8)) AS DateTimeStop, Left([Field3],Len([Field3])-1) AS IPAddress, Val(nz([Field6])) AS Seconds, 24 AS Stream
FROM [9072]
WHERE ((([9072].Field5)="closed")) and Val(nz([Field6])) >= 10 ;

Nic;o)

steveca

ASKER

Ahhhh!!! I just realized that I sent the wrong data values for Field6. It should be with the bracket at the beginning, like so:

(6787
(34
(8
(0
(3
(345

Of course this screws up what you suggested, Nico.

My appologies, folks.

Steve

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial