creating queries IN ACCESS using variables IN VB

[nuBudDy]

hi all, i have problem creating queries in access.this is because i do not know how to call the value from a variable in vb into the query in access.
let say in vb i've created :

Public booking_date As Date
Public fac_type As Integer
.
.
.
.
booking_date = MonthView1.value
fac_type = Combo1.ItemData

i suppose to have a query done in access which i will call in vb

SELECT TF.time_id
FROM TF
WHERE (((TF.fac_id)="fac_type") AND ((TF.reserv_date)="booking_date"));

how do i accomplish this?please help!
thanx in advnce!

[choudhuryshouvi]

hi all, i have problem creating queries in access.this is because i do not know how to call the value from a variable in vb into the query in access.
let say in vb i've created :

Public booking_date As Date
Public fac_type As Integer
.
.
.
.
booking_date = MonthView1.value
fac_type = Combo1.ItemData

i suppose to have a query done in access which i will call in vb

SELECT TF.time_id
FROM TF
WHERE (((TF.fac_id)="fac_type") AND ((TF.reserv_date)="booking_date"));

how do i accomplish this?please help!
thanx in advnce!

Hi, i've attached a sample program. download it. hope it will give you some idea.
regards
Shouvik

[nuBudDy]

thank you 4 the help choudry but what im trying to do is the reverse.instead of creating an sqlstatement in vb, i'd like to create query in access by using variables declared in vb.let say i code sql statement into query in access:

SELECT TF.time_id
FROM TF
WHERE (((TF.fac_id)="fac_type") AND ((TF.reserv_date)="booking_date"));

so when i'm done with this, i cant straight away call the query from access in vb.
the one in red is actually a variable declared in vb.
is this possible actually??
thanx in advance..

[debasisdas]

Try this

select tf.time_id from tf where(((tf.rac_id= "'" fac_type & "') And ((" & tf.reserv_date)= " & booking-date & "))"

[nuBudDy]

debasisdas,
i tried but it keeps giving me a syntax error.could something be missing?do u mean to write :
fac_type as

' " & fac_type & " '

and
booking_date as

' " & booking_date & " '

?

[choudhuryshouvi]

debasisdas,
i tried but it keeps giving me a syntax error.could something be missing?do u mean to write :
fac_type as

' " & fac_type & " '

and
booking_date as

' " & booking_date & " '

?

delete the trailing spaces from both side of the sql string. the correct syntax is:-

************for fac_type************
'" & fac_type & "'

************for booking_date************
'" & booking_date & "'

[nuBudDy]

i still can't get it right..i wonder is it actually possible for the query to retrieve values from variables in vb at vb run-time..:(

[yello]

If you are writing vbcode in the MSAccess module (access macro), then you can access the variable from vb.

Also one more thing to be noted here is the data type of 'fac_type'. If it is an integer type you don't need to wrap the double quotes with single quotes
" & fac_type & " will do.
But if fac_type is a string value you need to give '" & fac_type & "'

Now, If you are writing vbcode outside access and connecting to access using ADO or DAO, I dont think you can directly access the variable from access query. The right way is to build the query in vb and pass it to access.

Hope this helps.

[nuBudDy]

thank you very much for the infos.there's another thing i'm curious about.
usually,when we make an sql statement in vb,for an example :-

Adodc1.RecordSource = SELECT * FROM Student WHERE student_id = '"& id & "'

in this case, we get a single set of information of the student(name,age,add,course,etc....).this is because the id is unique.

is it possible for "Adodc1.RecordSource" to hold more than one set of infos retrieved from the table?
eg : make a query and retrieve 3 rows of info matching the criteria of my sql statement.

*i tried doing it but keeps getting an error.i wonder if this might be the cause of my error.:(

[plusplus]

of course adodc can hold more than one row of criteria
do you have
adodc1.refresh
after
adodc1.recordsource
????

[nuBudDy]

i had :

With Adodc2
.RecordSource = "SELECT time_id FROM TF WHERE fac_id = '" & fac_type & "' AND reserv_date = '" & booking_date & "'"
.Recordset.Requery*
.Refresh

but i get an error that says 'object variable or with block variable not set'
and the one that gets highlighted when i debug was line*
that's why i thought the error might be before the line * which was the query;(

[plusplus]

what's
.recordset.requery*
for?

maybe what you need is the following
with adodc2
.recordsource =(your select statement)
.refresh
end with

do while adodc2.recordset.eof = false
'whatever you want to do with the row found
adodc2.recordset.movenext
'moves to the next row found
loop

[nuBudDy]

i tried implementing the method given but still my query returns no result.:(
anyone care to have a look at this problem..?i've attached my codes here..

[plusplus]

I think there are a few problems with your code. Start with this, to compare dates you should use format$

"SELECT time_id FROM TF WHERE fac_id = " & fac_type & " AND reserv_date = " & Format$(booking_date, "\#m\/d\/yyyy\#")

then at all the following lines change the
else
if
to elseif and then have only one endif at the end
and change all the .recordset.fields(" ") to just .recordset(" ")

If .Recordset.Fields("time_id").Value = 8 Then
Option1.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 9 Then
Option2.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 10 Then
Option3.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 11 Then
Option4.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 12 Then
Option5.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 13 Then
Option6.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 14 Then
Option7.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 15 Then
Option8.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 16 Then
Option9.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 17 Then
Option10.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 18 Then
Option11.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 19 Then
Option12.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 20 Then
Option13.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 21 Then
Option14.Enabled = False
Else
If .Recordset.Fields("time_id").Value = 22 Then
Option15.Enabled = False
Else
Option16.Enabled = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

what I still don't understand is what do you need
.recordset.requery
for???????

[plusplus]

maybe you don't have to change
.recordset.field
to just
.recordset
I just do it that way

Anyway, where in your code do you fill your datagrid?

[nuBudDy]

an addition:
i tried making queries with only one criteria which was

with Adodc2
.RecordSource = "SELECT time_id FROM TF WHERE fac_id = " & fac_type & ""
.Refresh
end with

there is a result in return.it works fine.

but when i try making queries with 2 criterias

With Adodc2
.RecordSource = "SELECT time_id FROM TF WHERE fac_id = '" & fac_type & "' AND reserv_date = " & booking_date & ""
.Recordset.Requery
.Refresh

or using only booking_date as the criteria,it returns no result.i tried checking the datatype of booking_date from access table and variable i've declared in vb.
both are of type date.
i can't seem to find where it went wrong but for sure it has something to do with dates.

anyone who care to help,thanx x10000 in advance..

[plusplus]

why don't you try the select statement I wrote before, you have to use format$ in your select statement to compare dates
I tried it in your program you sent and it works, here it is again
"SELECT time_id FROM TF WHERE fac_id = " & fac_type & " AND reserv_date = " & Format$(booking_date, "\#m\/d\/yyyy\#")

[nuBudDy]

i got a sample program from the net that uses requery and it works fine in my login form.that's why i thought it is crucial when making queries(not knowing the real flow of it).i've altered my codes as told by you and everything went smoothly.thank you very much plusplus:)n also to all who had helped,thank you:)

[plusplus]

I'm happy I was able to help. You see, I just started working in vb(and programming in general) about half a year ago and my first project was a calender, all your questions sound familiar, I also had to work through them.

[kehar]

try with this

rs.open "select tf.time_id from tf where tf.rac_id= val('"& fac_type & "') And tf.reserv_date= cdate('" & booking-date & ")"