Query problem

[salman1karim]

Hi friends,

I face a problem with sql query in vb 6. I took some data from MSAcess database using the sql query which is mentioned below:

Set rs1 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where date1 = " & "'" & txtdate.Text & "'")

its gives Run time error no 3464 Data type mismatch cretira expression
if i write this query like

Set rs1 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where date1 = " & txtdate.Text )

Run time error '94' invalid use of null

please help me about this problem.

In MS Acess Database i use the date1 filed with the short date like(MM/DD/YYYY)
Thnx in Advance

[scripto]

Hi friends,

I face a problem with sql query in vb 6. I took some data from MSAcess database using the sql query which is mentioned below:

Set rs1 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where date1 = " & "'" & txtdate.Text & "'")

its gives Run time error no 3464 Data type mismatch cretira expression
if i write this query like

Set rs1 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where date1 = " & txtdate.Text )

Run time error '94' invalid use of null

please help me about this problem.

In MS Acess Database i use the date1 filed with the short date like(MM/DD/YYYY)
Thnx in Advance

test for a valid date in txtdate.text

if IsDate(txtdate. Text) then
Set rs1 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where date1 = ' " & cdate(txtdate.T ext ) & " ' ")
else
' do some error stuff here
end if

[Killer42]

test for a valid date in txtdate.text

if IsDate(txtdate. Text) then
Set rs1 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where date1 = ' " & cdate(txtdate.T ext ) & " ' ")
else
' do some error stuff here
end if

Not sure how important this is, but I believe the date (assuming it's present, of course:)) should have hashes around it. In other words,

...sale where date1 = [B]#[/B]" & txtdate.Text & "[B]#[/B]")

[scripto]

Not sure how important this is, but I believe the date (assuming it's present, of course:)) should have hashes around it. In other words,

...sale where date1 = [B]#[/B]" & txtdate.Text & "[B]#[/B]")

it's very important if we're using an ACCESS database - i kinda got the impression he was moving from ACCESS to SQL Svr. ???

[Killer42]

it's very important if we're using an ACCESS database - i kinda got the impression he was moving from ACCESS to SQL Svr. ???

Could be - I'm not familiar with SQL server, so can't really comment. Are they very different in handling date fields?

One thing I did notice – if this was actual code copied and pasted, then I believe the field name “quatity” is mis-spelled in either the database or the code.

[salman1karim]

Thanks reply for all guys.

I solve my problem and specially thanks for Killer42 woh give me the correct answer.