Pulling top values

[DuMont]

Hi Guys,

I have a textbox & button that when entered & pushed - run a query for the top values randomly depending on the value entered into the textbox.

My code is...

strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _
         "[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _
         "FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between " & Me.StartDate.Value & " And " & Me.EndDate.Value & " ORDER BY Rnd([service_id]) DESC "

It runs like it should but doesn't populate any records...that is until I go into the design view, deselect one of the fields and then reselect the same one - only then does it populate.

Do I need to open the query first and then update/append instead?

Thanks,

 

[CJ_London]

what are you doing with the sqlstr? Assigning it to a form recordsource or a querydef?

You need to show more of your vba code to be able to answer the question

 

[llkhoutx]

The dates don't appear to be concantenated correctly. They should be enclosed with the #.

 

[DuMont]

Full code is...

Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = CurrentDb.QueryDefs("Group1-CAN Top")

strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _
         "[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _
         "FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between " & Me.StartDate.Value & " And " & Me.EndDate.Value & " ORDER BY Rnd([service_id]) DESC "

Debug.Print strSQL
qdf.SQL = strSQL


DoCmd.OpenQuery "Group1-CAN Top"

Set db = Nothing
Set qdf = Nothing

It works as in it opens the query but no results are returned UNLESS I go into the design view, deselect a field, reselect the field and then view and it shows! I don't seem to get it...

 

[llkhoutx]

Post and examine the value of strSQL which does not work. I suspect that something wrong with the string.

 

[CJ_London]

I agree with llkhoutx - at least try it and see if it solves the problem

I don't use your methodology but you may also need to save the query before opening it