Solved Dmax SQL in VBA (1 Viewer)

[tmyers]

This may be slightly bad practice, but I am doing a small SQL statement in a module of mine and am having an issue with using DMax.

The code I am trying to put together is:

        Dim quotenumber As Long

            quotenumber = Left(Me.QuoteNumTxtbx, 5)
      
        strsql = " Update tblQuoteNumGeneration"
        strsql = strsql & " SET RevisionNum = nz(dmax("[RevisionNum]", "tblQuoteNumGeneration", "quotenumber")

The variable [quotenumber] was just an easy way I went with to find the first 5 digits of the quote (essentially the "primary" numbers). I then try to pass that into the DMax as my criteria.

The issue I am having is that I keep getting a compile error "Expected end of statement" and then it highlights [RevisionNum] in the DMax block. Why is that?

 

[tmyers]

Actually taking a step back and looking at it, it is probably because I tried an update, which isn't correct. I dont need to update, but add a new record. Must need more coffee.

 

[tmyers]

I am still having a problem with this one.
Now I am getting "Expected parameters = (some number).

        strsql = " INSERT INTO tblQuoteNumGeneration ( JobID, BaseQuote, Yearnum, RevisionNum )"
        strsql = strsql & " SELECT JobID = [Forms]![JobQuote]![JobID], BaseQuote = " & QuoteNumber & ", YearNum = " & MyYear & ", RevisionNum = " & NewRev & ""
        strsql = strsql & " FROM tblQuoteNumGeneration"

I think it may be how I am trying to pull the JobID that is causing it, but I would think that would cause a parameter popup wanting input if it didn't work.

The string output from debug.print:
INSERT INTO tblQuoteNumGeneration ( JobID, BaseQuote, Yearnum, RevisionNum )
SELECT JobID = [Forms]![JobQuote]![JobID], BaseQuote = 14937, YearNum = 20, RevisionNum = 2
FROM tblQuoteNumGeneration

Edit:
The FROM statement I added after the issue to see if that would fix it. It did not.

 

[Gasman]

I would expect you to concatenate the form value as you have for the other fields?

Debug.Print strSQL and see what is produced.?

 

[tmyers]

I would expect you to concatenate the form value as you have for the other fields?

Debug.Print strSQL and see what is produced.?

As is, Debug.Print output:
INSERT INTO tblQuoteNumGeneration ( JobID, BaseQuote, Yearnum, RevisionNum )
SELECT JobID = [Forms]![JobQuote]![JobID], BaseQuote = 14937, YearNum = 20, RevisionNum = 2
FROM tblQuoteNumGeneration

I will try to change how I derive JobID and see what it does.

 

[tmyers]

I just defined JobID in a variable like the others, and all it did was reduce the expected parameters error to 1

Debug:
INSERT INTO tblQuoteNumGeneration ( JobID, BaseQuote, Yearnum, RevisionNum )
SELECT JobID = 195, BaseQuote = 14937, YearNum = 20, RevisionNum = 2
FROM tblQuoteNumGeneration

 

[Isaac]

does this work?

strsql = " INSERT INTO tblQuoteNumGeneration ( JobID, BaseQuote, Yearnum, RevisionNum ) values(" & [Forms]![JobQuote]![JobID] & ", " & QuoteNumber & ", " & MyYear & ", " & NewRev & ")"

Are all those columns numeric type?

 

[tmyers]

does this work?

strsql = " INSERT INTO tblQuoteNumGeneration ( JobID, BaseQuote, Yearnum, RevisionNum ) values(" & [Forms]![JobQuote]![JobID] & ", " & QuoteNumber & ", " & MyYear & ", " & NewRev & ")"

Are all those columns numeric type?

They are.
I finally had an example that helped me figure it out. I had two problems. One was BaseQuote should have been BaseQuoteNum (whoops).
The second problem was how I was doing my values for the append. Rather than doing them like JobID = "variable", just do the variable.

So my SELECT statement changed to:
SELECT " & ID & ", " & QuoteNumber & ", " & MyYear & ", " & NewRev & "
And it worked.

 

[Isaac]

They are.
I finally had an example that helped me figure it out. I had two problems. One was BaseQuote should have been BaseQuoteNum (whoops).
The second problem was how I was doing my values for the append. Rather than doing them like JobID = "variable", just do the variable.

So my SELECT statement changed to:
SELECT " & ID & ", " & QuoteNumber & ", " & MyYear & ", " & NewRev & "
And it worked.

Bingo.
I'd never seen alias=value before in MS Access, although common in sql server, so instead of researching that I just switched to Values (which is what I always use when doing dynamic sql vba), but glad to hear you got yours working too

 

[tmyers]

I still apparently did the DMax incorrectly lol.
I just ran a couple test, and it didn't do the criteria correctly. I did one job 3 times, which output 12345-20-4, which is correct.
Then I went to a different job and tried it. Its new number should have been 12345-20-2, but it became 12345-20-5.

My DMax variable is:
QuoteNumber = Left(Me.QuoteNumTxtbx, 5)
OldRev = Nz(DMax("[RevisionNum]", "tblQuoteNumGeneration", QuoteNumber))
Do I happen to need to flip revisionnum and quotenumber?

 

[Isaac]

what's the plain english requirement for the dmax? you literally just want the max, right?

 

[Isaac]

OldRev = Nz(DMax("[RevisionNum]", "tblQuoteNumGeneration", "[somefieldname]=" & QuoteNumber),0)

I also added a second Nz argument. You have to supply what do you want if it's null? I put zero, but you might be diff

 

[tmyers]

I just tried to find the highest [RevisionNum] for a particular [QuoteNum]

So if quote number 12345 has 4 revisions, my next one needs to be 5.
But if quote 13579 has 7, my next needs to be 8.
Does that help?

 

[tmyers]

OldRev = Nz(DMax("[RevisionNum]", "tblQuoteNumGeneration", "[somefieldname]=" & QuoteNumber),0)

I also added a second Nz argument. You have to supply what do you want if it's null? I put zero, but you might be diff

That did it.
One of these days I will get good at doing this.
Hopefully.

 

[Isaac]

Just remember "the last portion of a domain aggregate function (dmax, dcount, dlookup, dmin) is the same as: the literal string of a 'where' clause, without the word 'where'

 

[tmyers]

Just remember "the last portion of a domain aggregate function (dmax, dcount, dlookup, dmin) is the same as: the literal string of a 'where' clause, without the word 'where'

That makes it much easier to understand.