my query doesnt work

[DL]

Hi,
I'm using this query in VBA:
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid='"& CurrentUser() & "' "

im using it like this:
Set dbs = CurrentDb
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid= '" & CurrentUser() & "'"
Set Some_Recordset = dbs.OpenRecords et(SQL_Str)

But i'm getting no records?

I know there are records to show.

Can anybody help me???
I've already spent two hours+ looking on this one...

[Douglas J. Steele]

Since you're delimiting Forms!TijdReg.[datumveld] with #, I assume datum is
a date field. What's the content of Forms!TijdReg.[datumveld]? Regardless of
what you've set the short date format to in your Regional Settings, it must
be in mm/dd/yyyy format (or else an unambiguous format such as dd mmm yyyy
or yyyy-mm-dd)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"DL" <da*******@hotm ail.com> wrote in message
news:40******** *************@n ews.wanadoo.nl. ..

Hi,
I'm using this query in VBA:
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid='"& CurrentUser() & "' "

im using it like this:
Set dbs = CurrentDb
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid= '" & CurrentUser() & "'"
Set Some_Recordset = dbs.OpenRecords et(SQL_Str)

But i'm getting no records?

I know there are records to show.

Can anybody help me???
I've already spent two hours+ looking on this one...

[James Fortune]

"DL" <da*******@hotm ail.com> wrote in message news:<40******* **************@ news.wanadoo.nl >...

Hi,
I'm using this query in VBA:
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid='"& CurrentUser() & "' "

im using it like this:
Set dbs = CurrentDb
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid= '" & CurrentUser() & "'"
Set Some_Recordset = dbs.OpenRecords et(SQL_Str)

But i'm getting no records?

I know there are records to show.

Can anybody help me???
I've already spent two hours+ looking on this one...

If pid is a Long rather than text you don't use the single quotes:
Forms!TijdReg.[datumveld] & "# AND pid= " & CurrentUser() & ";"

Access doesn't automatically convert names into key fields so if
CurrentUser() returns a text field you have to retrieve the pid using
a JOIN in your SQL statement or some other method like a lookup
function.

James A. Fortune

[G.J. v.d. Kamp]

Hi Daantje,

I presume you're from Holland or somewhere near, so the problem will
probably be the date format, wich is dd/mm/yyyy in dutch and
mm/dd/yyyy in the Access database. To correct this use the Format()
function:
SQL_Str = SQL_Str = "SELECT pid FROM registratie WHERE datum =#" &

Format(Forms!Ti jdReg.[datumveld],"mm/dd/yyyy")

& "# AND pid='"& CurrentUser() & "' "

(All on one line, i just cut out the relevant part for illustration)
That should work just fine.

Good luck,

Gert-Jan


"DL" <da*******@hotm ail.com> wrote in message news:<40******* **************@ news.wanadoo.nl >...

Hi,
I'm using this query in VBA:
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid='"& CurrentUser() & "' "

im using it like this:
Set dbs = CurrentDb
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid= '" & CurrentUser() & "'"
Set Some_Recordset = dbs.OpenRecords et(SQL_Str)

But i'm getting no records?

I know there are records to show.

Can anybody help me???
I've already spent two hours+ looking on this one...

[Alan Webb]

DL,
CurrentUser should return a string representing the currently logged in user
in Access' security model. It is often Admin if security is not enabled.
Check & see what CurrentUser is returning to your code and make sure it is
what was intended. I'd bet you are getting a value that doesn't match what
you expected it to be and thus won't get any records back.

"DL" <da*******@hotm ail.com> wrote in message
news:40******** *************@n ews.wanadoo.nl. ..

Hi,
I'm using this query in VBA:
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid='"& CurrentUser() & "' "

im using it like this:
Set dbs = CurrentDb
SQL_Str = "SELECT pid FROM registratie WHERE datum=#" &
Forms!TijdReg.[datumveld] & "# AND pid= '" & CurrentUser() & "'"
Set Some_Recordset = dbs.OpenRecords et(SQL_Str)

But i'm getting no records?

I know there are records to show.

Can anybody help me???
I've already spent two hours+ looking on this one...