using a sql-string to fill a variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I'm making a dbase in which the following statement works:

Selection = "INSERT INTO Temp (Potnr,LotID) SELECT Max(Pot.Potnr) AS
MaxOfPotnr, Pot.LotID FROM Pot GROUP BY Pot.LotID HAVING (((Pot.LotID) = " &
Lotnummer & ")) ORDER BY Max(Pot.Potnr)"
DoCmd.SetWarnings False
DoCmd.RunSQL Selection
DoCmd.SetWarnings True

it shoots the max value of a Potnr (the number of a container) to the table
temp. But i would like to shoot the value of Potnr not to a table, but to a
variable, so i can use it to calculate with

any suggestions?
 
I've many times wished this feature would be directly available, but
unfortunately it's not. It's multistep, I'm afraid.

Step 1) Open the recordset

Step 2) Get to the appropriate record

Step 3) Assign the variable

I'm sure you understand that there's no need to use an append query for this
purpose; a simple select query is adequate.

Sam
 
But i would like to shoot the value of Potnr not to a
table, but to a variable, so i can use it to calculate with
Click to expand...

By the way, I strongly suspect that this query would run faster like
this, because you don't need all the grouping stuff if you are only
looking for one group.

"INSERT INTO Temp (Potnr, lotid) " & _
"SELECT MAX(PotNr), " & lotnummer & " " & _
" FROM Pot " & _
" WHERE LotID = " & lotnummer & " "


This is how to get the value(s) as originally specified:

jetSQL = "SELECT Max(Pot.Potnr) AS MaxOfPotnr, " & _
" Pot.LotID " & _
" FROM Pot " & _
" GROUP BY Pot.LotID " & _
" HAVING (((Pot.LotID) = " & Lotnummer & ")) " & _
" ORDER BY Max(Pot.Potnr)"

' don't forget to check the thing!
debug.assert vbyes=msgbox(jetSQL, "Is this okay", vbyesno)

' using DAO, it's faster and easier
dim rs as recordset
' only want a single use snapshot
set rs = db.Openrecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)

' make sure there was anything there at all
if not rs.BOF then
' get the answers
varMaxOfPotNr = rs("MaxOfPotnr")
varLotID = rs("LotID")

else
' signal nothing found
varMaxOfPotNr = Null
varLotID = Null

end if

' tidy up
rs.close


there is a faster way too:

jetCriterion = "LotID = " & lotnummer

varMaxOfPotNr = DMax("PotNr", "Pot", jetCriterion)



Hope that helps


Tim F
 
Thank you very much for the SQL-solution....

but the faster solution is absolute brilliant !!!!!!!

thanks very much!
 
Back
Top