Getting type mismatch error

[mstrjohn]

I am using access as the front end to a mysql database using linked tables. I have a table called dailytrx that has all the transactions, there are 2 fields that I am concerned with, a date and a time, both are text. the date yyyymmdd and the time HHmm. I need to filter these transactions to a 24 hour window starting at 11PM 2 days ago and ended at 11PM yesterday. this is what I tried doing.

SELECT dailytrx.*
FROM dailytrx
WHERE (((DateAdd("n",CInt(Right([TrxTime],2)),DateAdd("h",CInt(Left([TrxTime],2)), DateValue(Right(Left([TrxDate],6),2) & "/" & Right([TrxDate],2) & "/" & Left([TrxDate],4))))) Between DateAdd("h",-1,Date()-1) And DateAdd("h",-1,Date())));


When I try to run it comes back with a type mismatch in criteria. If any one can see the problem I’d appreciate it.

[ADezii]

I am using access as the front end to a mysql database using linked tables. I have a table called dailytrx that has all the transactions, there are 2 fields that I am concerned with, a date and a time, both are text. the date yyyymmdd and the time HHmm. I need to filter these transactions to a 24 hour window starting at 11PM 2 days ago and ended at 11PM yesterday. this is what I tried doing.

SELECT dailytrx.*
FROM dailytrx
WHERE (((DateAdd("n",CInt(Right([TrxTime],2)),DateAdd("h",CInt(Left([TrxTime],2)), DateValue(Right(Left([TrxDate],6),2) & "/" & Right([TrxDate],2) & "/" & Left([TrxDate],4))))) Between DateAdd("h",-1,Date()-1) And DateAdd("h",-1,Date())));


When I try to run it comes back with a type mismatch in criteria. If any one can see the problem I’d appreciate it.

The SQL seems to work fine in Access given the same Text restrictions on both the Date and Time Fields.