Visual Basic/vb6.0+ms access
QUESTION: Respected Sir,
I am developing an application in VB6.0 and Access.In that I have a doubt in working with data report.What sql query I should give in data environmentt to combine two tables [where billno is a common field in both the tables and in one table bill no is
repeating in that table it stores details of all the items sold from a shop in the other table only the total amount, billno and billdate is stored] .I have tried the follwing query
select billtable.Billno,billtable.billdate,billtable.total,billtable.vat,billtable.final ,completebill.itemname, completebill.rate,completebill.quantity,completebill.total from billtable equi join completebill where billtable.Billno=completebill.Billno
but the billno is repeating in the datareport .How will i generate a sales bill.If you can help me i would be grateful to you sir
ANSWER: put a dataenvironment on form and point it to your database
add a command
it its sql properties
in the textbox enter
Select * from testable where itemNo=?
'where testable is your table and itemNo is the fieldname
add a datareport and drag all the fields of the command to the report designer.
to the form add a textbox and a commandbuttong and add this code:
Private Sub Command1_Click()
'enter a value for the field in the textbox and click the commandbutton
'this could be an item from listbox.
if field is number maybe you add val(text1) or ascribe it to a variable, convert it and use in command.
do some checking out. it works fine
---------- FOLLOW-UP ----------
Thanks for u r reply. I have followed your method but it works for only bills which have one item. In case if a bill has multiple items the billno will be repeating in the table and in the report also it is repeating. what can I do to avoid repetition of bill no and billdate in data report.
this can be done only if the bill no and date fields are left blank for the second and further items in the invoice. ie bill no date item
1 1/1/ spanner
2 2/1 Tape
3 2/1 oil
and in data report drag the header to the page header. then you get the format you want.but if you enter the dates and bill nos they need to be repeated. maybe some method exists