Help With SQL Statment

[james_halliwell]

Hi veryone,

Hope you all are well, Please could someone help me i have cascading combo boxs, but from the drop down list the dont they dont seem to be in numerical order i think is somthing like ORDER BY but im not sure on how to add it to the code

New to access but trying my best, if someone could point me in the right direction of how the code works that would be perfect

Private Sub Combo0_AfterUpdate()
Me.Combo2.RowSource = "SELECT [hips_product].Product, [hips_product].Quantity, [hips_product].Sales_12_month, [hips_product].Sales_3_month FROM [hips_product] " & _
"WHERE [HOSPITAL NO & NAME] = '" & Me.Combo0 & "'"
Me.Combo2.Requery

End Sub


Its the [hips_product].Product that contains a product code ie 1124571, but it seems to put them in any order

 

[DCrake]

You need to Add the sort order to the sql

ORDER By ....

After the Where section.

David

 

[james_halliwell]

You need to Add the sort order to the sql

ORDER By ....

After the Where section.

David

Would it look like this

Private Sub Combo0_AfterUpdate()
Me.Combo2.RowSource = "SELECT [hips_product].Product, [hips_product].Quantity, [hips_product].Sales_12_month, [hips_product].Sales_3_month FROM [hips_product] " & _
"WHERE [HOSPITAL NO & NAME] = '" & Me.Combo0"'" ORDER BY [hips_product].products"
Me.Combo2.Requery

End Sub

Thanks for the quick reply

 

[khawar]

Try this

Me.Combo2.RowSource = "SELECT [hips_product].Product, [hips_product].Quantity, [hips_product].Sales_12_month, [hips_product].Sales_3_month FROM [hips_product] " & _
"WHERE [HOSPITAL NO & NAME] = '" & Me.Combo0 & "' ORDER BY [hips_product].products"
Me.Combo2.Requery

 

[james_halliwell]

Try this

Me.Combo2.RowSource = "SELECT [hips_product].Product, [hips_product].Quantity, [hips_product].Sales_12_month, [hips_product].Sales_3_month FROM [hips_product] " & _
"WHERE [HOSPITAL NO & NAME] = '" & Me.Combo0 & "' ORDER BY [hips_product].products"
Me.Combo2.Requery

Thanks for all the quick responces, i seem to be getting and error after i add the orderby it says complie error expexted end of statment

Any ideas

Private Sub Combo0_AfterUpdate()
Me.Combo2.RowSource = "SELECT [hips_product].Product, [hips_product].Quantity, [hips_product].Sales_12_month, [hips_product].Sales_3_month FROM [hips_product] " & _
"WHERE [HOSPITAL NO & NAME] = '" & Me.Combo0 & "'"ORDER BY [hips_product].products
Me.Combo2.Requery

End Sub

 

[JANR]

Use Khawar's code exemple, you've got quote error and space error in your code.

JR

 

[oumahexi]

I think you have your quote marks int he wrong place. Remember, you should always have open and closed quotes. You have two sets (open and close) just before ORDER BY on the last line, remove one and place it at the end of .products and before Me.Combo2Requery

 

[DCrake]

Me.Combo2.RowSource = "SELECT [hips_product].Product, [hips_product].Quantity, [hips_product].Sales_12_month, [hips_product].Sales_3_month FROM [hips_product] " & _
"WHERE [HOSPITAL NO & NAME] = '" & Me.Combo0 & "'"ORDER BY [hips_product].products

Should read:

Me.Combo2.RowSource = "SELECT Product, Quantity, Sales_12_month, Sales_3_month FROM [hips_product] " & _
"WHERE [HOSPITAL NO & NAME] = '" & Me.Combo0 & "' " & "ORDER BY products; "

David

 

[james_halliwell]

Re: Help With SQL Statment -- Resolved

Hi,

Problem solved it works perfectly thanks for all the fast replys and help given i have rep all who helped

Thanks again

James