DataSelection from Database

[mavtcr]

Dear friends pl help me
I want to select data from a data base table Tran with two selection criteria one is number and the other one is
text When i use one criteria it works well But i am failed to use the two criteria
my code is here for one criteria
`TRS.Open "SELECT * FROM Tran WHERE Acno =" & Val(TxtAsnno.Text), CN, adOpenStatic, adLockOptimistic

I want to use Acno=val(txtAcno.text) and Name= txtName.text

Please help me

[Learner010]

try this:

"select * from Tran where Acno=" & Val(TxtAcno.Text) & " and name='" & txtname.Text & "'"

hope this helps.

[mavtcr]

Thanks , and if the second criteria is a fixed string eg. "CASH"
ie. Name ="CASH" what changes has to be made in the above code?

[Reverend Jim]

"select * from Tran where Acno=" & TxtAcno.Text & " and name='CASH'"

[mavtcr]

Sorry one more doubt

1.If the second criteria is a variable date eg. txtDob.text
2.if it is a fixed date eg. "20-11-2013"
For both this what will be the code?

[Reverend Jim]

It depends on your database. Assuming your system date format is set to "dd-mm-yyyy" then for a SQL database you would use single quotes around the field just like in 'CASH', however for Access you would use hash marks as in #20-11-2013#

[mavtcr]

Sir, what you mean by the system date format?
1.The format used for the dates in the corresponding programme only
2. The dates used always by the computer in every other programmes or work
If it is the second how can we change the format to our need?
Excuse me if it seems silly...

[mavtcr]

Sir another problem,
When I wanted to select datas pertaining to today's date error occurs
I have given the following code
`TRS.Open "SELECT * FROM Tran WHERE Date = Date", CN, adOpenStatic, adLockOptimistic

Here the datas having different dates are selected,I want the datas of today only

Please help me

[mavtcr]

Dear Reverend Jim,Learner010 please help me

[Learner010]

try this:

"select * from Tran where Date=" & Date 

[Reverend Jim]

You have to delimit the date. In SQL you use single quotes and in Access you use hash marks. If you want to use the current date in a query you can always use a builtin date function. In SQL this is

SELECT * FROM Tran WHERE [Date] = GETDATE()

Note the use of square brackets in the field name because Date is a reserved word.

Also, when I use the term system date format I am referring to the format that you see when you display the date column in an explorer window set to details view. If a file/folder date is shown as 2013-11-23 then your system date format is YYYY-MM-DD.

[mavtcr]

Dear Learner010 I could not succeed. My database is in Access
The following results for each time, none of them fulfill my requirement
1.On using... "SELECT * from Tran WHERE Date=" & Date
Result...error.No value given for one or more required parameters
2.On using... "SELECT * from Tran WHERE Date=Date"
Result...All the records are selected irrespective of Date
3. On using... "SELECT * from Tran WHERE Date=" & txtDate.text
Here txtDate is current/today's date
Result...Every time the programme runs the record number is 1 though there are several records in the same date.
My aim is to generate continuous No. for a given date,ie. if I add six records the no of records must be 6 and when I add next day the record no must be 1.Any idea?
Another thing.. In my database the date column is in the format:dd-Mmm-yyyy
even though my given format through code is : dd/mm/yyyy Why the format is changed in the result? Does it affect my data selection? What is the remedy?
Thank you for your patience to read me.Sorry If I spoiled your time...

[Reverend Jim]

For Access you need

Dim qry As String = "SELECT * FROM Tran WHERE [Date]=#" & Date & "#"

Is your [Date] column actually declared as a date or is is declared as String (or char or varchar)?

[mavtcr]

Dear Reverend Jim,
Thank you very much for your help. I succeeded.
But I have a humble request, Please answer directly(specifically) to the question raised It will help much to beginners like me.
eg.When I ask if VB.net has inbuilt Visual data manager, You can very well say 'Yes/No' instead You said 'I prefer SQL' Likewise I asked the date formats whether it will affect or how affects the database etc You didn't explain directly.This confused me.
Still you helped me much ..Thanks a lot ..I expect still more from you...

[Reverend Jim]

I aim to please but I believe I answered every question put to me. There is no question in this thread having anything to do with an inbuilt Visual data manager. That question was in this thread. In that thread I said I was unaware of the visual data manager capabilities of vb.net but I suggested what I thought was an alternative option (and a far better one than relying on anything which may be bundled with Visual Studio). Anyone else browsing that thread with advice to the contrary was free to hop in. Since I was the only one who responded then my advice was all you got. Without that you would have none.

As for the date format, when asked, I explained what I meant by System Date Format and I attempted to get more information on your column definition in order to provide a more accurate answer.

Having said that, I will take your comments into consideration. I appreciate you taking the time to voice them.

[mavtcr]

Thanks a lot

[mavtcr]

Thank you once again sir