Correct SQL in Code Please

  • Thread starter Thread starter Chipmunkwhiz
  • Start date Start date
C

Chipmunkwhiz

I am trying to set up some code to renumber customer
lists. I am self taught, so I apologise if this is
impossible this way.

I am trying to set variables and then use them in an
update Query. Could someone set me on the right path?

Dim MaxID, MinID, ExistDif, MinNewID As Long
Dim StrSQL As String
'set variables
MaxID = DMax("ID", "Customer Table", "([customer table].
[Delivery Route])=[Forms]![MainMainform]![MainMenu].
[Form]![CustomerListFilter]")
MinID = DMin("ID", "Customer Table", "([customer table].
[Delivery Route])=[Forms]![MainMainform]![MainMenu].
[Form]![CustomerListFilter]")
ExistDif = MaxID - MinID
MinNewID = ([Forms]![MainMainform]![MainMenu].[Form]!
[CustomerListFilter] - 1) * 1000
'set SQL
StrSQL = "UPDATE [Customer Table] SET [Customer Table].ID
= ((1000 * ([Customer table].ID - MinID) / ExistDif) +
MinNewID) WHERE ((([customer table].[Delivery Route])=
[Forms]![MainMainform]![MainMenu].[Form]!
[CustomerListFilter]));"

CurrentDb.Execute StrSQL, [dbFailOnError]
 
You need to concatenate the variables into the string so that you get the
values of the variables, not the names of the variables as part of the text
in the SQL string.
StrSQL = "UPDATE [Customer Table] SET [Customer Table].ID
= ((1000 * ([Customer table].ID - MinID) / ExistDif) +
MinNewID) WHERE ((([customer table].[Delivery Route])=
[Forms]![MainMainform]![MainMenu].[Form]!
[CustomerListFilter]));"
Click to expand...

strSQL = UPDATE [Customer Table] SET [Customer Table].ID = ((1000 *
([Customer Table].ID - " & MinID & ") / ExistDif) + " & MinNewID & ") WHERE
((([Customer Table].[Delivery Route]) =
[Forms]![MainMainform]![MainMenu].[Form]![CustomerListFilter]));"

If the form referred to in the WHERE clause is open, you could concatenate
in that value as well or pass the path to the form's control as you are
doing and let the query retrieve it. Since it is a "physical" location the
query can find it, unlike a variable which is "just in memory".

--
Wayne Morgan
Microsoft Access MVP


Chipmunkwhiz said:
I am trying to set up some code to renumber customer
lists. I am self taught, so I apologise if this is
impossible this way.

I am trying to set variables and then use them in an
update Query. Could someone set me on the right path?

Dim MaxID, MinID, ExistDif, MinNewID As Long
Dim StrSQL As String
'set variables
MaxID = DMax("ID", "Customer Table", "([customer table].
[Delivery Route])=[Forms]![MainMainform]![MainMenu].
[Form]![CustomerListFilter]")
MinID = DMin("ID", "Customer Table", "([customer table].
[Delivery Route])=[Forms]![MainMainform]![MainMenu].
[Form]![CustomerListFilter]")
ExistDif = MaxID - MinID
MinNewID = ([Forms]![MainMainform]![MainMenu].[Form]!
[CustomerListFilter] - 1) * 1000
'set SQL
StrSQL = "UPDATE [Customer Table] SET [Customer Table].ID
= ((1000 * ([Customer table].ID - MinID) / ExistDif) +
MinNewID) WHERE ((([customer table].[Delivery Route])=
[Forms]![MainMainform]![MainMenu].[Form]!
[CustomerListFilter]));"

CurrentDb.Execute StrSQL, [dbFailOnError]
Click to expand...
 
I missed that ExistDif is also a variable. It will need to be concatenated
in as well.

Change
([Customer Table].ID - " & MinID & ") / ExistDif) + "
To
([Customer Table].ID - " & MinID & ") / " & ExistDif & ") + "
 
Back
Top