reteriving data from a table

[zivon]

its seems like the most simple question on access...

anyway, I have a table with reservations and I want to loop through it (with code..) and check which reservation is at a specific date.
from that ofcurse I want to get the name of the persone who made that reservation.

I hope someone could help me out.. and I'm sorry if I'm asking obvious questions...



sigh.. why is it so hard to find help in tutorials about it ?

[ADezii]

its seems like the most simple question on access...

anyway, I have a table with reservations and I want to loop through it (with code..) and check which reservation is at a specific date.
from that ofcurse I want to get the name of the persone who made that reservation.

I hope someone could help me out.. and I'm sorry if I'm asking obvious questions...



sigh.. why is it so hard to find help in tutorials about it ?

ASSUMPTIONS:

1. Your Table Name is tblReservations consisting of the following Fields:
   1. Date - [DATE/TIME]
   2. First - [TEXT]
   3. Last - [TEXT]
2. The SQL would be:
   

   SELECT Date, First, Last
   FROM tblReservations
   WHERE tblReservations.Date=[Enter Reservation Date (MM/DD/YYYY)];

[zivon]

well... I know SQL.. but it doesn't work on VBA...
I already tried that..
looks like VBA doesn't recognaiz SELECT as a valid command..

[zivon]

ok, thanks, I mannaged to do the query... but now I have another weird problam..

when I try:

SELECT CustomerName, CkInDate from Orders WHERE Orders.CustomerName='Idan'"

it works with no problams.. but when I'm trying a date, it doesn't work..
I tried this both:

1) SELECT CustomerName, CkInDate from Orders WHERE Orders.CkInDate='31/08/2007'"

this gives me an error..

2)
CkDate = "31/08/2008"
SELECT CustomerName, CkInDate from Orders WHERE Orders.CkInDate=" & CkDate

this one doesn't give an error, but it doesn't find any record... and I checked milion times, there is a recored CkInDate equal to 31/08/2008

[zivon]

btw, to help others who are trying the same thing:


Function GOR(d As String) As String

Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim GOrder As String

'Open connection to current Access database
Set db = CurrentDb()

'Create SQL statement to retrieve value from GST table
Dim CkDate As Date
CkDate = "22/08/2008"
LSQL = "select CustomerName, CkInDate from orders WHERE orders.CkInDate=" & CkDate

Set Lrs = db.OpenRecordset(LSQL)

'Retrieve value if data is found
GOrder = "Not Found"
Do While Lrs.EOF = False
GOrder = Lrs("CkInDate")
' GOrder = Lrs("CustomerName")
Lrs.MoveNext
Loop

Lrs.Close
Set Lrs = Nothing
GOR = GOrder
End Function

[Scott Price]

ok, thanks, I mannaged to do the query... but now I have another weird problam..

when I try:

SELECT CustomerName, CkInDate from Orders WHERE Orders.CustomerName='Idan'"

it works with no problams.. but when I'm trying a date, it doesn't work..
I tried this both:

1) SELECT CustomerName, CkInDate from Orders WHERE Orders.CkInDate='31/08/2007'"

this gives me an error..

2)
CkDate = "31/08/2008"
SELECT CustomerName, CkInDate from Orders WHERE Orders.CkInDate=" & CkDate

this one doesn't give an error, but it doesn't find any record... and I checked milion times, there is a recored CkInDate equal to 31/08/2008

Date literals must be enclosed in # signs. #31/03/2008#

Regards,
Scott