Incorrect Data Type?!?

[jpinto]

Hello,

I've the following code:

TotalVendaTable.RecordSource = "SELECT * FROM Vendas WHERE ((Vendas.Data > '" & DeData & "' ) AND (Vendas.Data < '" & AData & "')) ORDER BY Vendas.Data ASC"

TotalVendaTable.Refresh

where I receive error nr. 3464: "Incorrect Data Type in criteria Expression" (I translated from the Portuguese message error!)

Can annyone help me please?

Thanks,

João Pinto

 

[jebenson]

The code Vendas.Data > '" & DeData & "' implies that Vendas.Data is a numeric field, and you have the criteria enclosed in single quotes, as if it is a string value. Try removing the single quotes from this and from the other criteria expression.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[jpinto]

I've changed my code. Now is like this:

TotalVendaTable.RecordSource = "SELECT * FROM Vendas WHERE ((Vendas.Data > DeData) AND (Vendas.Data < AData)) ORDER BY Vendas.Data ASC"

TotalVendaTable.Refresh

This way I don't receive the previous error message but I got this:

Error 3061: "Few parameters. 2 expected"

Can you help me again please.

Thanks,

João

 

[Golom]

Now you've gone to the other extreme. What jebenson was suggesting was something like

TotalVendaTable.RecordSource = 
"SELECT * FROM Vendas 
WHERE ((Vendas.Data > " & DeData & " ) AND 
       (Vendas.Data < " & AData & ")) 
ORDER BY Vendas.Data ASC"

You get the error message because it is assuming that "DeData" and "AData" are fields in the table rather than external variables.

 

[jpinto]

Ok, thanks for the help.

I've done like you told me. Now I don't get an error message but I don't receive any data from my database! I put the following data:

Textbox "DaData" = "15-12-2004"
Textbox "AData" = "22-12-2004"

I Know I've records on my database between this 2 dates but I don't get any records displayed!

Can it be because I've on the database, the field "Data" formated like "18-12-2004 15:30:23"?

Any sugestion?

Thanks,

Joao

 

[jpinto]

Can annyone help please?

 

[Golom]

Ah-Ha!

Your fields are dates!

You need

TotalVendaTable.RecordSource = "SELECT * FROM Vendas WHERE ((Vendas.Data > #" & DeData & "# ) AND (Vendas.Data  < #" & AData & "#)) ORDER BY Vendas.Data ASC"

 

[jpinto]

I've already got there. I've the following code:

TotalVendaTable.RecordSource = "SELECT * FROM Vendas WHERE ((Vendas.Data >= #" & DeData & "#) AND (Vendas.Data <= #" & AData & "#)) ORDER BY Vendas.Data ASC"

TotalVendaTable.Refresh

if DeData=11-12-2004 and AData=20-12-2004 I receive the following records:

18-11-2004 14:05:35
18-11-2004 14:06:09
23-11-2004 16:19:16
12-12-2004 15:56:45
15-12-2004 16:51:59
...

if DeData=12-12-2004 and AData=20-12-2004 I receive the following records:

12-12-2004 15:56:45
15-12-2004 16:51:59
15-12-2004 18:13:39
...

This very strange! Can annyone give me a clue of whats happening with my database?

Thanks,

João

 

[AndyWatt]

Try:

TotalVendaTable.RecordSource = "SELECT * FROM Vendas WHERE ((Vendas.Data >= #" & Format(CDate(DeData), "MM/DD/YYYY") & "#) AND (Vendas.Data <= #" & Format(CDate(AData), "MM/DD/YYYY") & "#)) ORDER BY Vendas.Data ASC"

Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[jpinto]

Thanks AndyWatt! That did resolve my problem. You deserve a star.

João

 

[BobRodes]

I'm getting the feeling that there could be some kind of problem with the international settings. If the input 11-12-2004 is interpreted the American way of mm-dd-yyyy, the resultset would be correct. Perhaps what happens is that SQL server automatically converts values such as 18-11-2004, that can't evaluate to a valid date using the American order, to 11-18-2004, and leaves the others alone. Such behavior is consistent with the results you describe, anyway. At least, it's something to look at.

HTH

Bob

 

[AndyWatt]

This looks like Access to me Bob. SQL Server would be happy with the "'" around dates, but Access sure doesn't like them.

In my experience Access disregards any date regional settings and will not cope with anything except mm/dd/yyyy format.

Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[BobRodes]

Good point, Andy.

Bob

 

[Golom]

It's a documented (or so I'm told) characteristic of Access that it doesn't use locale settings when interpreting a date of the form xx/yy/zz. It does use mm/dd/yy if both "xx" and "yy" are <= 12 (i.e. US convention.) Output of dates ... converting from datetime to a text representation does use locale settings. The fix for this is either cave in and set locale to mm/dd/yy (not an option in some parts of the world) or use an explicit conversion so that Access doesn't need to guess.

Things like DateSerial ( 04, 05, 02 ) or 02-May-04 will get you around the problem.

 

[johnwm]

jpinto
You say

Thanks AndyWatt! That did resolve my problem. You deserve a star.

Why don't you give him a star? Just click on the link marked as 'Thank AndyWatt
for this valuable post!' at the bottom of one of his answers.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts