SQL help

[rebecky via AccessMonster.com]

I have a Main Form for Employer Information with a subform for Job Order
Information. On the Main form there is a search box to search for "Industry
Type" that actually resides in the Job Order Information Table that is bound
to the subform. I have the search box select from a query made up of the two
tables(employer Information and Job Order Information)and I am finding the
industry type the user enters. I want the results to be sorted by a field
called "open/filled" in descending order but I cannot get it to work!!! I
don't really know if the "Like" statement is even right. So if the user
types in Office and hits the search button, I want ALL office jobs with jobs
whose status is "open" or "pending" on top. Seems like a small thing but I
can't get it right.
Thanks in advance for any suggestions.

Search button on click event:

Me.RecordSource = "SELECT * FROM Query7 " _
& "WHERE [IndustryType] Like ""*" & Me.Text574 & "*""" & _
" ORDER BY 'open/filled' desc;"


[Form_DataEntry subform].RecordSource = "SELECT * FROM Query3 " _
& "WHERE [IndustryType] Like ""*" & [form_EmployerPositions1].Text574 &
"*""" & _
" ORDER BY 'open/filled' desc;"

If Not IsNull(Me!Text574) And IsNull([Form_DataEntry subform].[IndustryType])
Then


MsgBox "What Position Type were You Looking for? Please Try
Again."

End If

 

[Dirk Goldgar]

If "open/filled" is the name of a field, then the ORDER BY clause you have
like this:

" ORDER BY 'open/filled' desc;"

should be like this instead:


" ORDER BY [open/filled] desc;"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I have a Main Form for Employer Information with a subform for Job Order
Information. On the Main form there is a search box to search for
"Industry
Type" that actually resides in the Job Order Information Table that is
bound
to the subform. I have the search box select from a query made up of the
two
tables(employer Information and Job Order Information)and I am finding the
industry type the user enters. I want the results to be sorted by a field
called "open/filled" in descending order but I cannot get it to work!!! I
don't really know if the "Like" statement is even right. So if the user
types in Office and hits the search button, I want ALL office jobs with
jobs
whose status is "open" or "pending" on top. Seems like a small thing but
I
can't get it right.
Thanks in advance for any suggestions.

Search button on click event:

Me.RecordSource = "SELECT * FROM Query7 " _
& "WHERE [IndustryType] Like ""*" & Me.Text574 & "*""" & _
" ORDER BY 'open/filled' desc;"


[Form_DataEntry subform].RecordSource = "SELECT * FROM Query3 " _
& "WHERE [IndustryType] Like ""*" & [form_EmployerPositions1].Text574 &
"*""" & _
" ORDER BY 'open/filled' desc;"

If Not IsNull(Me!Text574) And IsNull([Form_DataEntry
subform].[IndustryType])
Then


MsgBox "What Position Type were You Looking for? Please Try
Again."

End If

 

[rebecky via AccessMonster.com]

Thank you! Such a little thing to cause such big distress!

It works now.

If "open/filled" is the name of a field, then the ORDER BY clause you have
like this:

" ORDER BY 'open/filled' desc;"

should be like this instead:

" ORDER BY [open/filled] desc;"

I have a Main Form for Employer Information with a subform for Job Order
Information. On the Main form there is a search box to search for

[quoted text clipped - 33 lines]

End If