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]

Re: my query doesnt work

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" <daantje_l@hotm ail.com> wrote in message
news:40e5b08a$0 $233$4a441750@n ews.wanadoo.nl. ..[color=blue]
> 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...
>
>[/color]

[James Fortune]

Re: my query doesnt work

"DL" <daantje_l@hotm ail.com> wrote in message news:<40e5b08a$ 0$233$4a441750@ news.wanadoo.nl >...[color=blue]
> 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...[/color]

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]

Re: my query doesnt work

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" <daantje_l@hotm ail.com> wrote in message news:<40e5b08a$ 0$233$4a441750@ news.wanadoo.nl >...[color=blue]
> 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...[/color]

[Alan Webb]

Re: my query doesnt work

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" <daantje_l@hotm ail.com> wrote in message
news:40e5b08a$0 $233$4a441750@n ews.wanadoo.nl. ..[color=blue]
> 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...
>
>[/color]