variable in stringquery?

  • Thread starter Thread starter Henro
  • Start date Start date
H

Henro

I have the following code
How can I replace the #12/7/2004# with the variable Verjaardag?

[Forms].[Engineer].[Verjaardag] doesn't work although it is noted like that
in the SQL expression :-(


Private Sub Form_close()

Dim Verjaardag As Date
Dim db As Database
Dim Voorwaarde As DAO.Recordset

Verjaardag = Me.Verjaardag

Set db = CurrentDb
Set Voorwaarde = db.OpenRecordset("SELECT Agenda.Begindatum,
Agenda.Engineer, Agenda.Omschrijving FROM Agenda WHERE
(((Agenda.Begindatum)= #12/7/2004#) AND ((Agenda.Omschrijving) is Null));")
If Voorwaarde.RecordCount = 0 Then
With Me.RecordsetClone
.AddNew
!Omschrijving = "Gebak"
!Engineer = NaamEngineer()
!Begindatum = Verjaardag
![Verwerkt in agenda] = False
!Loos = True
.Update
MsgBox Msg, vbOKOnly, Titel
End With
Else: End

End If
End Sub
 
You need to concatenate your variable into the SQL string

At:
....(((Agenda.Begindatum)= #12/7/2004#) AND...

Try:

....(((Agenda.Begindatum)= #" & Verjaardag & "#) AND...

BTW, you need to do the same thing to pick up values from a TextBox on a form.

For a TextBox on a form different from where the code lives try:

....(((Agenda.Begindatum)= #" & Forms!Engineer!Verjaardag & "#) AND...

If the code is running on the same form where the TextBox lives, all you need is:

....(((Agenda.Begindatum)= #" & Verjaardag & "#) AND...

This assumes you don't have a variable in your code with the same name as the TextBox. This is where using naming conventions keeps
you out of trouble.

Your TextBox should be named txtVerjaardag and your variable should be named datVerjaardag (or dtmVerjaardag.)

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
I have the following code
How can I replace the #12/7/2004# with the variable Verjaardag?
Click to expand...

Well, despite your Subject line, this *ISN'T* a stringquery. A
Date/Time value isn't a string!
[Forms].[Engineer].[Verjaardag] doesn't work although it is noted like that
in the SQL expression :-(


Private Sub Form_close()

Dim Verjaardag As Date
Dim db As Database
Dim Voorwaarde As DAO.Recordset

Verjaardag = Me.Verjaardag

Set db = CurrentDb
Set Voorwaarde = db.OpenRecordset("SELECT Agenda.Begindatum,
Agenda.Engineer, Agenda.Omschrijving FROM Agenda WHERE
(((Agenda.Begindatum)= #12/7/2004#) AND ((Agenda.Omschrijving) is Null));")
Click to expand...

Try

(((Agenda.Begindatum)=" &
Format(CDate([Forms].[Engineer].[Verjaardag]),"mm/dd/yyyy") & " AND
((Agenda.Omschrijving) is Null));")

Note that a Date/Time literal MUST be either in American
month/day/year format, or in an unambiguous format such as
12-July-2004.
 
Back
Top