Problem with concatenating in SQL statement

[Glenley]

I'm having some trouble with my code. I bolded and underlined the section in question. We do drug testing and I'm slowly trying to build a random generator form. I'm kind of a noob when it comes to VBA so be easy on me. TIA

Private Sub btnTest_Click()


Dim db As Database
Dim rsC As Recordset
Dim Cust As Long

StatusBox = ""
Cust = Forms!RandomF!CustomerCombo.Column(0)
Set db = CurrentDb
Set rsC = db.OpenRecordset("SELECT Rnd([DonorID]*Now()) AS X, DonorFirstName, DonorLastName, OrganizationID " _
& "FROM DonorT " _
& "WHERE OrganizationID='" & Cust & "'" ORDER BY X DESC)

While Not rsC.EOF
Status rsC!X & ":" & rsC!DonorFirstName & " " & rsC!DonorLastName
rsC.MoveNext
Wend

rsC.Close
db.Close
Set rsC = Nothing
Set db = Nothing



End Sub

[Glenley]

And YES I indent my code, it just didn't paste the indentation

[orange]

Try the following:
& "WHERE OrganizationID=" & Cust & " ORDER BY X DESC)"

You don't need quotes with numbers.

[Micron]

And YES I indent my code, it just didn't paste the indentation

That's one reason why we often tell people to use code tags (# on posting toolbar, on the far right side).

[Welshgasman]

Put all the sql into a string variable. Then you can debug.print it and see what you actually have, not what you think you have.
When correct, then use that variable n the recordset.

Plus if you cannot get it correct you can copy and paste here and someone can advise what you have done wrong?

What is is supposed to do anyway?

[Glenley]

The variable prints correctly (I'm guessing at the terminology). My Organization ID is what it's supposed to be. The problem arises when add the sort to the SQL statement. I tried:
& "WHERE OrganizationID=" & Cust & " ORDER BY X DESC) but I get a syntax error. If I take the sort completely out, it runs fine

[davegri]

If I take the sort completely out, it runs fine

You can't sort on X, a calculated alias.
You will have to

ORDER BY Rnd([DonorID]*Now()) DESC

However, this might calculate a different number from the previous, which would make the sort useless.

[Glenley]

Thank you, that worked. I'm okay with the sort being changed. It needs to be random. Now I need to look into the Randomize ​function