SQL String

C

c.kurutz

Hi. I am working on an Access database that someone else developed.
I have the following code in the database. I need to modify the SQL
string statement to also prompt for a date range based on the
"carddate". Any help would be greatly appreciated:

Private Sub changecpw_Click()
Dim ilong As Long
Dim retval As Long
Dim holdthis As Long
Dim tempquerydef As QueryDef
Dim tempdb As Database
Dim sqlstring As String
Dim matchthis As String
Dim newtext As String
Dim ctrlarray(1 To 2) As Control

retval = 0
holdthis = 0
For ilong = 1 To 2
retval = isvalid(ilong, ctrlarray())
If retval Then
holdthis = holdthis + 2 ^ retval
End If
Next
If holdthis = 0 Then
ordernumber.setfocus
matchthis = Trim$(ordernumber.Text)
ilong = howmany(matchthis)
If ilong > 0 Then
Set tempdb =
DBEngine.Workspaces(0).OpenDatabase(currentdatabase)
DBEngine.Workspaces(0).BeginTrans
complete.setfocus
newtext = Trim$(complete.Text)
'***PLACE I NEED TO FIX....
sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34)" AND cardtable!carddate= BETWEEN [Enter
Start Date] and [Enter End Date]";"

Set tempquerydef = tempdb.CreateQueryDef("", sqlstring)
tempquerydef.Execute
tempquerydef.Close
DBEngine.Workspaces(0).CommitTrans
tempdb.Close
Set tempdb = Nothing
End If
complete.setfocus
complete.Text = ""
ordercombo.setfocus
ordercombo.Text = ""
ordernumber.setfocus
ordernumber.Text = ""
Else
For ilong = 1 To 2
If (holdthis And (2 ^ ilong)) = 2 ^ ilong Then
Exit For
End If
Next
ctrlarray(ilong).setfocus
For ilong = 1 To 2
Set ctrlarray(ilong) = Nothing
Next
End If
End Sub
 
F

fredg

Hi. I am working on an Access database that someone else developed.
I have the following code in the database. I need to modify the SQL
string statement to also prompt for a date range based on the
"carddate". Any help would be greatly appreciated:

Private Sub changecpw_Click()
Dim ilong As Long
Dim retval As Long
Dim holdthis As Long
Dim tempquerydef As QueryDef
Dim tempdb As Database
Dim sqlstring As String
Dim matchthis As String
Dim newtext As String
Dim ctrlarray(1 To 2) As Control

retval = 0
holdthis = 0
For ilong = 1 To 2
retval = isvalid(ilong, ctrlarray())
If retval Then
holdthis = holdthis + 2 ^ retval
End If
Next
If holdthis = 0 Then
ordernumber.setfocus
matchthis = Trim$(ordernumber.Text)
ilong = howmany(matchthis)
If ilong > 0 Then
Set tempdb =
DBEngine.Workspaces(0).OpenDatabase(currentdatabase)
DBEngine.Workspaces(0).BeginTrans
complete.setfocus
newtext = Trim$(complete.Text)
'***PLACE I NEED TO FIX....
sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34)" AND cardtable!carddate= BETWEEN [Enter
Start Date] and [Enter End Date]";"

Set tempquerydef = tempdb.CreateQueryDef("", sqlstring)
tempquerydef.Execute
tempquerydef.Close
DBEngine.Workspaces(0).CommitTrans
tempdb.Close
Set tempdb = Nothing
End If
complete.setfocus
complete.Text = ""
ordercombo.setfocus
ordercombo.Text = ""
ordernumber.setfocus
ordernumber.Text = ""
Else
For ilong = 1 To 2
If (holdthis And (2 ^ ilong)) = 2 ^ ilong Then
Exit For
End If
Next
ctrlarray(ilong).setfocus
For ilong = 1 To 2
Set ctrlarray(ilong) = Nothing
Next
End If
End Sub
Click to expand...

Dim dteStart as Date
Dim dteEnd as Date

dteStart = InputBox("Enter the start date")
dteEnd = InputBox("Enter End date")

sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & " AND cardtable!carddate BETWEEN #" &
dteStart & "# and #" & dteEnd & "#;"

1) If you use Between .. And, then you do not use = Between.
2) Dates need to be delimited with the date symbol #.
3) I also added a & after the last chr(34).
 
J

John Spencer

You need to get the start date and end date from somewhere and store them in
variables. Then try the following.

By the way the date strings should be in mm/d/yyyy format or in yyyy/mm/dd
format to preclude any problems.

sqlstring = "UPDATE cardtable " & _
" SET cardtable!complete=" & Chr$(34) & newtext & Chr$(34) & _
" WHERE cardtable!ordernumber=" & Chr$(34) & matchthis & Chr$(34)" &
" AND cardtable!carddate BETWEEN #" & txtStartDate & _
"# and #" & txtEndDate & "#;"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
C

c.kurutz

Hi. I am working on an Access database that someone else developed.
I have the following code in the database. I need to modify the SQL
string statement to also prompt for a date range based on the
"carddate". Any help would be greatly appreciated:
Click to expand...
Private Sub changecpw_Click()
Dim ilong As Long
Dim retval As Long
Dim holdthis As Long
Dim tempquerydef As QueryDef
Dim tempdb As Database
Dim sqlstring As String
Dim matchthis As String
Dim newtext As String
Dim ctrlarray(1 To 2) As Control
Click to expand...
retval = 0
holdthis = 0
For ilong = 1 To 2
retval = isvalid(ilong, ctrlarray())
If retval Then
holdthis = holdthis + 2 ^ retval
End If
Next
If holdthis = 0 Then
ordernumber.setfocus
matchthis = Trim$(ordernumber.Text)
ilong = howmany(matchthis)
If ilong > 0 Then
Set tempdb =
DBEngine.Workspaces(0).OpenDatabase(currentdatabase)
DBEngine.Workspaces(0).BeginTrans
complete.setfocus
newtext = Trim$(complete.Text)
'***PLACE I NEED TO FIX....
sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34)" AND cardtable!carddate= BETWEEN [Enter
Start Date] and [Enter End Date]";"
Click to expand...
Set tempquerydef = tempdb.CreateQueryDef("", sqlstring)
tempquerydef.Execute
tempquerydef.Close
DBEngine.Workspaces(0).CommitTrans
tempdb.Close
Set tempdb = Nothing
End If
complete.setfocus
complete.Text = ""
ordercombo.setfocus
ordercombo.Text = ""
ordernumber.setfocus
ordernumber.Text = ""
Else
For ilong = 1 To 2
If (holdthis And (2 ^ ilong)) = 2 ^ ilong Then
Exit For
End If
Next
ctrlarray(ilong).setfocus
For ilong = 1 To 2
Set ctrlarray(ilong) = Nothing
Next
End If
End Sub
Click to expand...

Dim dteStart as Date
Dim dteEnd as Date

dteStart = InputBox("Enter the start date")
dteEnd = InputBox("Enter End date")

sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & " AND cardtable!carddate BETWEEN #" &
dteStart & "# and #" & dteEnd & "#;"

1) If you use Between .. And, then you do not use = Between.
2) Dates need to be delimited with the date symbol #.
3) I also added a & after the last chr(34).
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you very much. That did it! I appreciate the fix.
 
C

clk

Hi. I am working on anAccessdatabase that someone else developed.
I have the following code in the database. I need to modify the SQL
string statement to also prompt for a date range based on the
"carddate". Any help would be greatly appreciated:
Private Sub changecpw_Click()
Dim ilong As Long
Dim retval As Long
Dim holdthis As Long
Dim tempquerydef As QueryDef
Dim tempdb As Database
DimsqlstringAs String
Dim matchthis As String
Dim newtext As String
Dim ctrlarray(1 To 2) As Control
retval = 0
holdthis = 0
For ilong = 1 To 2
retval = isvalid(ilong, ctrlarray())
If retval Then
holdthis = holdthis + 2 ^ retval
End If
Next
If holdthis = 0 Then
ordernumber.setfocus
matchthis = Trim$(ordernumber.Text)
ilong = howmany(matchthis)
If ilong > 0 Then
Set tempdb =
DBEngine.Workspaces(0).OpenDatabase(currentdatabase)
DBEngine.Workspaces(0).BeginTrans
complete.setfocus
newtext = Trim$(complete.Text)
'***PLACE I NEED TO FIX....
sqlstring= "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34)" AND cardtable!carddate= BETWEEN [Enter
Start Date] and [Enter End Date]";"
Set tempquerydef = tempdb.CreateQueryDef("",sqlstring)
tempquerydef.Execute
tempquerydef.Close
DBEngine.Workspaces(0).CommitTrans
tempdb.Close
Set tempdb = Nothing
End If
complete.setfocus
complete.Text = ""
ordercombo.setfocus
ordercombo.Text = ""
ordernumber.setfocus
ordernumber.Text = ""
Else
For ilong = 1 To 2
If (holdthis And (2 ^ ilong)) = 2 ^ ilong Then
Exit For
End If
Next
ctrlarray(ilong).setfocus
For ilong = 1 To 2
Set ctrlarray(ilong) = Nothing
Next
End If
End Sub
Click to expand...
Click to expand...
Dim dteStart as Date
Dim dteEnd as Date
Click to expand...
dteStart = InputBox("Enter the start date")
dteEnd = InputBox("Enter End date")
Click to expand...
sqlstring= "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & " AND cardtable!carddate BETWEEN #" &
dteStart & "# and #" & dteEnd & "#;"
Click to expand...
1) If you use Between .. And, then you do not use = Between.
2) Dates need to be delimited with the date symbol #.
3) I also added a & after the last chr(34).
Click to expand...
- Show quoted text -
Click to expand...

Thank you very much. That did it! I appreciate the fix.- Hide quoted text -

- Show quoted text -
Click to expand...


I have more to add. They only want the date range to be requested if
the "new text" is becoming a "P" or "W" if it was previously a "C".
If it is a new "P" or "W" then it can go through without a date
range. Any help would be appreciated.
 
C

clk

On Tue, 13 Nov 2007 12:16:08 -0800, (e-mail address removed) wrote:
Hi. I am working on anAccessdatabase that someone else developed.
I have the following code in the database. I need to modify theSQL
stringstatement to also prompt for a date range based on the
"carddate". Any help would be greatly appreciated:
Private Sub changecpw_Click()
Dim ilong As Long
Dim retval As Long
Dim holdthis As Long
Dim tempquerydef As QueryDef
Dim tempdb As Database
DimsqlstringAsString
Dim matchthis AsString
Dim newtext AsString
Dim ctrlarray(1 To 2) As Control
retval = 0
holdthis = 0
For ilong = 1 To 2
retval = isvalid(ilong, ctrlarray())
If retval Then
holdthis = holdthis + 2 ^ retval
End If
Next
If holdthis = 0 Then
ordernumber.setfocus
matchthis = Trim$(ordernumber.Text)
ilong = howmany(matchthis)
If ilong > 0 Then
Set tempdb =
DBEngine.Workspaces(0).OpenDatabase(currentdatabase)
DBEngine.Workspaces(0).BeginTrans
complete.setfocus
newtext = Trim$(complete.Text)
'***PLACE I NEED TO FIX....
sqlstring= "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34)" AND cardtable!carddate= BETWEEN [Enter
Start Date] and [Enter End Date]";"
Set tempquerydef = tempdb.CreateQueryDef("",sqlstring)
tempquerydef.Execute
tempquerydef.Close
DBEngine.Workspaces(0).CommitTrans
tempdb.Close
Set tempdb = Nothing
End If
complete.setfocus
complete.Text = ""
ordercombo.setfocus
ordercombo.Text = ""
ordernumber.setfocus
ordernumber.Text = ""
Else
For ilong = 1 To 2
If (holdthis And (2 ^ ilong)) = 2 ^ ilong Then
Exit For
End If
Next
ctrlarray(ilong).setfocus
For ilong = 1 To 2
Set ctrlarray(ilong) = Nothing
Next
End If
End Sub
Dim dteStart as Date
Dim dteEnd as Date
dteStart = InputBox("Enter the start date")
dteEnd = InputBox("Enter End date")
sqlstring= "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & " AND cardtable!carddate BETWEEN #" &
dteStart & "# and #" & dteEnd & "#;"
1) If you use Between .. And, then you do not use = Between.
2) Dates need to be delimited with the date symbol #.
3) I also added a & after the last chr(34).
Click to expand...
Click to expand...
Click to expand...


This is the code I have:

Private Sub changecpw_Click()
Dim ilong As Long
Dim retval As Long
Dim holdthis As Long
Dim tempquerydef As QueryDef
Dim tempdb As Database
Dim sqlstring As String
Dim matchthis As String
Dim newtext As String
Dim ctrlarray(1 To 2) As Control
Dim dteStart As Date
Dim dteEnd As Date

retval = 0
holdthis = 0
For ilong = 1 To 2
retval = isvalid(ilong, ctrlarray())
If retval Then
holdthis = holdthis + 2 ^ retval
End If
Next
If holdthis = 0 Then
ordernumber.setfocus
matchthis = Trim$(ordernumber.Text)
ilong = howmany(matchthis)
If ilong > 0 Then

Set tempdb =
DBEngine.Workspaces(0).OpenDatabase(currentdatabase)
DBEngine.Workspaces(0).BeginTrans
complete.setfocus
newtext = Trim$(complete.Text)
If newtext = "P" Or "W" And cardtable!complete = "C" Then
dteStart = InputBox("Enter the Start Date")
dteEnd = InputBox("Enter the End Date")
sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & " AND cardtable!carddate BETWEEN #" &
dteStart & "# and #" & dteEnd & "#;"
Else
sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & ";"
End If
Set tempquerydef = tempdb.CreateQueryDef("", sqlstring)
tempquerydef.Execute
tempquerydef.Close
DBEngine.Workspaces(0).CommitTrans
tempdb.Close
Set tempdb = Nothing
End If
complete.setfocus
complete.Text = ""
ordercombo.setfocus
ordercombo.Text = ""
ordernumber.setfocus
ordernumber.Text = ""
Else
For ilong = 1 To 2
If (holdthis And (2 ^ ilong)) = 2 ^ ilong Then
Exit For
End If
Next
ctrlarray(ilong).setfocus
For ilong = 1 To 2
Set ctrlarray(ilong) = Nothing
Next
End If
End Sub


I need it to recognize if it is being changed to a P or W check to see
if it was previously a C. If so prompt for date range, otherwise make
change.

I think it is this line that is not working:

If newtext = "P" Or "W" And cardtable!complete = "C" Then

It seems to just make the change all the time even when it was
previously a "C".

Any help would be greatly appreciated.
 
J

John Spencer

You have to make a complete comparison.

If (newtext = "P" Or NewText = "W") And cardtable!complete = "C" Then


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
C

clk

You have to make a complete comparison.

If (newtext = "P" Or NewText = "W") And cardtable!complete = "C" Then

--
John SpencerAccessMVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.















- Show quoted text -
Click to expand...

Thank you for the input. I tried that line of code. I am getting a
"variable not defined" and the "cardtable" portion is highlighted.

If (newtext = "P" Or newtext = "W") And cardtable!complete = "C" Then

I keep trying different pieces of code but nothing seems to work. Any
thoughts would be greatly appreciated.
 
J

John Spencer

It is totally unclear to me where cardTable!complete comes from.

It appears you are attempting to reference a field in a table - is there a
recordset that has this value or is this taking place on a form? I can't
decipher what your current code looks like either.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

">
 
C

clk

It is totally unclear to me where cardTable!complete comes from.

It appears you are attempting to reference a field in a table - is there a
recordset that has this value or is this taking place on a form? I can't
decipher what your current code looks like either.

--
John SpencerAccessMVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

">






- Show quoted text -
Click to expand...

There is a piece of code that sets the recordset on the form. It is
below.

Private Sub Form_Activate()
Dim dummy As String
Dim ilong As Long
If isopen = 0 Then
isopen = -1
If Len(usethisdatabase) = 0 Then
Call setupdatabase
End If
Set thisdatabase =
DBEngine.Workspaces(0).OpenDatabase(usethisdatabase, False, False)
Set cardrecordset = thisdatabase.OpenRecordset("cardtable",
dbOpenDynaset)
If Not cardrecordset.BOF Then
cardrecordset.MoveFirst
End If
If Not cardrecordset.EOF Then
cardrecordset.MoveLast
End If
Set employrecordset =
thisdatabase.OpenRecordset("employtable", dbOpenDynaset, dbReadOnly)
Set drawingrecordset =
thisdatabase.OpenRecordset("dwgnumbertable", dbOpenDynaset,
dbReadOnly)
Set machinerecordset =
thisdatabase.OpenRecordset("machinetable", dbOpenDynaset, dbReadOnly)
Set shiftrecordset = thisdatabase.OpenRecordset("shifttable",
dbOpenDynaset, dbReadOnly)
End If
ilong = setnumlock()
End Sub

"cardrecordset" is using the table that I need to reference.

Below is the code used for the update:

Private Sub changecpw_Click()
Dim ilong As Long
Dim retval As Long
Dim holdthis As Long
Dim tempquerydef As QueryDef
Dim tempdb As Database
Dim sqlstring As String
Dim matchthis As String
Dim newtext As String
Dim ctrlarray(1 To 2) As Control
Dim dteStart As Date
Dim dteEnd As Date



retval = 0
holdthis = 0
For ilong = 1 To 2
retval = isvalid(ilong, ctrlarray())
If retval Then
holdthis = holdthis + 2 ^ retval
End If
Next
If holdthis = 0 Then
ordernumber.setfocus
matchthis = Trim$(ordernumber.Text)
ilong = howmany(matchthis)
If ilong > 0 Then

Set tempdb =
DBEngine.Workspaces(0).OpenDatabase(currentdatabase)
DBEngine.Workspaces(0).BeginTrans
complete.setfocus

newtext = Trim$(complete.Text)
If (newtext = "P" Or newtext = "W") And (((cardtable.complete)
= C)) Then
dteStart = InputBox("Enter the Start Date")
dteEnd = InputBox("Enter the End Date")
sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & " AND cardtable!carddate BETWEEN #" &
dteStart & "# and #" & dteEnd & "#;"
Else
sqlstring = "UPDATE cardtable SET cardtable!complete=" & Chr
$(34) & newtext & Chr$(34) & " WHERE cardtable!ordernumber=" & Chr
$(34) & matchthis & Chr$(34) & ";"
End If
Set tempquerydef = tempdb.CreateQueryDef("", sqlstring)
tempquerydef.Execute
tempquerydef.Close
DBEngine.Workspaces(0).CommitTrans
tempdb.Close
Set tempdb = Nothing
End If
complete.setfocus
complete.Text = ""
ordercombo.setfocus
ordercombo.Text = ""
ordernumber.setfocus
ordernumber.Text = ""
Else
For ilong = 1 To 2
If (holdthis And (2 ^ ilong)) = 2 ^ ilong Then
Exit For
End If
Next
ctrlarray(ilong).setfocus
For ilong = 1 To 2
Set ctrlarray(ilong) = Nothing
Next
End If
End Sub


Any help is appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Building/Storing a passthrough:How to assign connection string andmake passthrough 3
Need help in printing vba code 0
passing a parameter based on multiple selections 7
qurey to listbox help 2
Error: Compile Error,ByRef argument type mismatch 0
How do I automatically open link in email? Outlook macro 1
PowerPoint Shuffle (swap) objects on PPT slides with the same name only 0
VBA Multiple Line handling with Split() 1

Top