Where Clause in Insert Into statement......

  • Thread starter Thread starter Todd MacPhee
  • Start date Start date
T

Todd MacPhee

Hello, and thank you to anyone who can help with this. I
am trying to struggle through programming databases with
vb on my own, and I'm finding it hard.

I have a form, with a listbox, which is populated by
seting its rowsource property to another list box. I have
the MultiSelect property set to Simple, which is why i
used the counter. The following Code is meant to retreive
the autonumbered primary key for each record, based on
criteria set by the selection(s) from the list box, and 2
text fields with start and end dates in them.

'// Clear The Table for the Matches
Call CurrentDb.Execute("Delete * from TblTempReport") '//
Populate It Dim counter As Long Dim sql As String Dim
Driver As String
Call DoCmd.Hourglass(True)

For counter = 0 To Me.lstDrivers.ItemsSelected.Count -
1
Driver = Me.lstDrivers.ItemData
(lstDrivers.ItemsSelected(counter))

'// fill the temp table...
sql = "INSERT INTO tbltemp (numrecordnumber)
SELECT [tblhours].[aunrecordnumber]" & _
"FROM [tblhours] " & _
"WHERE tblhours.[txtname]= " & Driver & " and
[tblhours].[dtmdate] between #" & Me.txtStartDate & "# and
#" & Me.txtEndDate & "#'"

Call CurrentDb.Execute(sql)
Next counter

Call DoCmd.Hourglass(False)


It wont work though, I keep getting an error with the
message " Syntax Error (missing Operator) in query
expression."

Thoughts ?

Todd
 
"WHERE tblhours.[txtname]= '" & Driver & "' AND
[tblhours].[dtmdate] Between #" & Me.txtStartDate & "# and
#" & Me.txtEndDate & "#'"

You need single quotes around Driver.
 
Still giving me a syntax error?

-----Original Message-----
"WHERE tblhours.[txtname]= '" & Driver & "' AND
[tblhours].[dtmdate] Between #" & Me.txtStartDate & "# and
#" & Me.txtEndDate & "#'"

You need single quotes around Driver.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Hello, and thank you to anyone who can help with this. I
am trying to struggle through programming databases with
vb on my own, and I'm finding it hard.

I have a form, with a listbox, which is populated by
seting its rowsource property to another list box. I have
the MultiSelect property set to Simple, which is why i
used the counter. The following Code is meant to retreive
the autonumbered primary key for each record, based on
criteria set by the selection(s) from the list box, and 2
text fields with start and end dates in them.

'// Clear The Table for the Matches
Call CurrentDb.Execute("Delete * from TblTempReport") '//
Populate It Dim counter As Long Dim sql As String Dim
Driver As String
Call DoCmd.Hourglass(True)

For counter = 0 To Me.lstDrivers.ItemsSelected.Count -
1
Driver = Me.lstDrivers.ItemData
(lstDrivers.ItemsSelected(counter))

'// fill the temp table...
sql = "INSERT INTO tbltemp (numrecordnumber)
SELECT [tblhours].[aunrecordnumber]" & _
"FROM [tblhours] " & _
"WHERE tblhours.[txtname]= " & Driver & " and
[tblhours].[dtmdate] between #" & Me.txtStartDate & "# and
#" & Me.txtEndDate & "#'"

Call CurrentDb.Execute(sql)
Next counter

Call DoCmd.Hourglass(False)


It wont work though, I keep getting an error with the
message " Syntax Error (missing Operator) in query
expression."

Thoughts ?

Todd
Click to expand...


.
Click to expand...
 
It appears that you have an extra apostrophe after the last #.

todd macphee said:
Still giving me a syntax error?

-----Original Message-----
"WHERE tblhours.[txtname]= '" & Driver & "' AND
[tblhours].[dtmdate] Between #" & Me.txtStartDate & "# and
#" & Me.txtEndDate & "#'"

You need single quotes around Driver.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Hello, and thank you to anyone who can help with this. I
am trying to struggle through programming databases with
vb on my own, and I'm finding it hard.

I have a form, with a listbox, which is populated by
seting its rowsource property to another list box. I have
the MultiSelect property set to Simple, which is why i
used the counter. The following Code is meant to retreive
the autonumbered primary key for each record, based on
criteria set by the selection(s) from the list box, and 2
text fields with start and end dates in them.

'// Clear The Table for the Matches
Call CurrentDb.Execute("Delete * from TblTempReport") '//
Populate It Dim counter As Long Dim sql As String Dim
Driver As String
Call DoCmd.Hourglass(True)

For counter = 0 To Me.lstDrivers.ItemsSelected.Count -
1
Driver = Me.lstDrivers.ItemData
(lstDrivers.ItemsSelected(counter))

'// fill the temp table...
sql = "INSERT INTO tbltemp (numrecordnumber)
SELECT [tblhours].[aunrecordnumber]" & _
"FROM [tblhours] " & _
"WHERE tblhours.[txtname]= " & Driver & " and
[tblhours].[dtmdate] between #" & Me.txtStartDate & "# and
#" & Me.txtEndDate & "#'"

Call CurrentDb.Execute(sql)
Next counter

Call DoCmd.Hourglass(False)


It wont work though, I keep getting an error with the
message " Syntax Error (missing Operator) in query
expression."

Thoughts ?

Todd
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top