Query problem

[salman1karim]

Hi guys.

I face one little problem in query. i took some data from access write sql query but some they took data some time not or some time asked null data but i dont know how to put the check basis so please help me about this problem. the query is mentioned below:

using the DAO:

Set rs2 = db.OpenRecordse t("sale")
Set rs2 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where invoice = " & "'" & txtsearch.Text & "'")
If rs2.EOF = True Then
Exit Sub
Else

txttotal.Text = rs2(0)********* *here gve NUll or no database error******
txttotal.Text = Format(txttotal .Text, "####0.000" )
End If

Set rs3 = db.OpenRecordse t("sale")
Set rs3 = db.OpenRecordse t("select discount from sale where invoice = " & "'" & txtsearch.Text & "'")

If rs3.EOF = True Then
Exit Sub
Else
txtDiscount.Tex t = rs3(0)********* *here gve NUll or no database error******
txtDiscount.Tex t = Format(txtDisco unt.Text, "####0.000" )
txttotal.Text = rs2(0) - rs3(0)
txttotal.Text = Format(txttotal .Text, "####0.000" )
End If

so please let me know how to put the correct if else check.
thnx in advance

[Killer42]

I'm afraid I didn't really understand the question. However, don't the index numbers of fields start at 1? If so, then

txttotal.Text = rs2([U][B]0[/B][/U])

would be expected to produce an error. Perhaps you would be better advised to use the field name rather than the position number. Also, why are you opening each recordset twice? It seems to me that these two lines...

Set rs2 = db.OpenRecordset("sale") Set rs2 = db.OpenRecordset("select sum(foodprice * quatity) from sale where invoice = " & "'" & txtsearch.Text & "'")

would be better condensed down to ...

Set rs2 = db.OpenRecordset("select sum(foodprice * quatity) from sale where invoice = '" & txtsearch.Text & "'")

Oh, and one more thing. You still have "quatity" spelled incorrectly (I think).

[salman1karim]

I'm afraid I didn't really understand the question. However, don't the index numbers of fields start at 1? If so, then

txttotal.Text = rs2([U][B]0[/B][/U])

would be expected to produce an error. Perhaps you would be better advised to use the field name rather than the position number. Also, why are you opening each recordset twice? It seems to me that these two lines...

Set rs2 = db.OpenRecordset("sale") Set rs2 = db.OpenRecordset("select sum(foodprice * quatity) from sale where invoice = " & "'" & txtsearch.Text & "'")

would be better condensed down to ...

Set rs2 = db.OpenRecordset("select sum(foodprice * quatity) from sale where invoice = '" & txtsearch.Text & "'")

Oh, and one more thing. You still have "quatity" spelled incorrectly (I think).

Hi ,

this is correct spelling of quatity and for my question
i face problem with my conditions because the first query take the multiplication with the food and quantity and put in the text box.so u understand why i use the indexing like rs(0) but if i put the wrong invoice number so it gives error so i face problem with my if else conditions so please let me know how to handle my problem.

thanks for reply

[yogesh2522]

Hi guys.

I face one little problem in query. i took some data from access write sql query but some they took data some time not or some time asked null data but i dont know how to put the check basis so please help me about this problem. the query is mentioned below:

using the DAO:

Set rs2 = db.OpenRecordse t("sale")
Set rs2 = db.OpenRecordse t("select sum(foodprice * quatity) from sale where invoice = " & "'" & txtsearch.Text & "'")
If rs2.EOF = True Then
Exit Sub
Else

txttotal.Text = rs2(0)********* *here gve NUll or no database error******
txttotal.Text = Format(txttotal .Text, "####0.000" )
End If

Set rs3 = db.OpenRecordse t("sale")
Set rs3 = db.OpenRecordse t("select discount from sale where invoice = " & "'" & txtsearch.Text & "'")

If rs3.EOF = True Then
Exit Sub
Else
txtDiscount.Tex t = rs3(0)********* *here gve NUll or no database error******
txtDiscount.Tex t = Format(txtDisco unt.Text, "####0.000" )
txttotal.Text = rs2(0) - rs3(0)
txttotal.Text = Format(txttotal .Text, "####0.000" )
End If

so please let me know how to put the correct if else check.
thnx in advance

Set rs2 = db.OpenRecordse t("sale")
Set rs2 = db.OpenRecordse t("select sum(foodprice * quatity) from sale
where invoice = " & "'" & txtsearch.Text & "'")

Here you have written wrong Condition in query.
is Invoice is Text Field or Numeric
If it is Numeric then you write it as "& txtsearch.text &"
if it is text then '"& txtsearch.text &"'

[salman1karim]

Set rs2 = db.OpenRecordse t("sale")
Set rs2 = db.OpenRecordse t("select sum(foodprice * quatity) from sale
where invoice = " & "'" & txtsearch.Text & "'")

Here you have written wrong Condition in query.
is Invoice is Text Field or Numeric
If it is Numeric then you write it as "& txtsearch.text &"
if it is text then '"& txtsearch.text &"'

The user put the invoice # inside the text box (like txtsearch.text) and then after the query will be run and took data from the database. but when the invoice # is correct then find it but when invoice # is incorrect then give the error like null value or 0 or not find data.(in this line txtbox.text=rs( 0) ) so please help me about my problem.

thanks

[salman1karim]

Guys plz help me about my problem

[aaryan]

Guys plz help me about my problem

hi salman,
would really like to help you. frankly speaking i cd not understand your problem.
so leave the code. just post back your structure of the table, the desired input and the output you expect from the program.

[salman1karim]

hi salman,
would really like to help you. frankly speaking i cd not understand your problem.
so leave the code. just post back your structure of the table, the desired input and the output you expect from the program.

Thanks for your reply

I Face a lot of problem and at last i solve my solution.

and thanks for all guys.