SQL String

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(currentdatabas e)
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

On Tue, 13 Nov 2007 12:16:08 -0800, wrote:

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(currentdatabas e)
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).
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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
..

wrote in message
oups.com...
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(currentdatabas e)
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

On Nov 13, 3:37 pm, fredg wrote:
On Tue, 13 Nov 2007 12:16:08 -0800, wrote:
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(currentdatabas e)
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).
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

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

On Nov 13, 5:32 pm, wrote:
On Nov 13, 3:37 pm, fredg wrote:





On Tue, 13 Nov 2007 12:16:08 -0800, wrote:
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(currentdatabas e)
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).
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

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

- Show quoted text -


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.

On Nov 16, 10:52 am, clk wrote:
On Nov 13, 5:32 pm, wrote:





On Nov 13, 3:37 pm, fredg wrote:

On Tue, 13 Nov 2007 12:16:08 -0800, 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(currentdatabas e)
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).
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -



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(currentdatabas e)
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.

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
..

"clk" wrote in message
...
On Nov 16, 10:52 am, clk wrote:
On Nov 13, 5:32 pm, wrote:





On Nov 13, 3:37 pm, fredg wrote:

On Tue, 13 Nov 2007 12:16:08 -0800, wrote:



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(currentdatabas e)
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.

On Nov 29, 11:16 am, "John Spencer" wrote:
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
.

"clk" wrote in message

...



On Nov 16, 10:52 am, clk wrote:
On Nov 13, 5:32 pm, wrote:

On Nov 13, 3:37 pm, fredg wrote:

On Tue, 13 Nov 2007 12:16:08 -0800, wrote:

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 AsString
Dim matchthis AsString
Dim newtext AsString
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(currentdatabas e)
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.- Hide quoted text -

- Show quoted text -

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.

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
..

"
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.

On Dec 5, 7:48 am, "John Spencer" wrote:
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
.

"



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.- Hide quoted text -

- Show quoted text -

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(usethisdatabas e, 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(currentdatabas e)
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.