problem with Listbox rowquery

[Associates]

Hi,

i have a problem to ask.

I have the following codes in the form

Private Sub Form_Load()
strSQL = "SELECT [Jobs].JobID, [SubJobs].IndustryNo, [SubJobs].ClientNo,
[SubJobs].JobNo, [SubJobs].SubJobNo, IIf(Not IsNull([SubJobName]) Or
[SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status"
strSQL = strSQL & " FROM [SubJobs] INNER JOIN [Jobs] ON ([SubJobs].JobNo
= [Jobs].JobNo) AND ([SubJobs].ClientNo = [Jobs].ClientNo) AND
([SubJobs].IndustryNo = [Jobs].IndustryNo)"
strSQL = strSQL & " WHERE ((([SubJobs].Status) = -1))"

Me!ListBox_Jobs.RowSource = strSQL
Me!ListBox_Jobs.Requery

Me.Refresh
End Sub

The list-box is empty when the form is loaded. I must've done something
wrong here. Could anyone help me out?

Thank you in advance

 

[Jeanette Cunningham]

Hi,
add the line
Debug.Print strSQL
just above the line
Me!ListBox_Jobs.RowSource = strSQL

run the form
press Ctl + G to open the immediate window and see what access has for
strSQL
Copy and paste strSQL into the SQL view of a new query.
Switch to datasheet view of the query, which will show you the errors in the
query.
When you get the SQL correct, the listbox should show the list of items.

you don't need the line
Me!ListBox_Jobs.Requery

and I can't see why you would need the line
Me.Refresh

Jeanette Cunningham

 

[Associates]

Thank you Jeanette for your reply.

Yes, i did as you suggested.

strSQL = "SELECT [Jobs].JobID, [SubJobs].IndustryNo,
[SubJobs].ClientNo,
[SubJobs].JobNo, [SubJobs].SubJobNo, IIf(Not IsNull([SubJobName]) Or
[SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status"
strSQL = strSQL & " FROM [SubJobs] INNER JOIN [Jobs] ON
([SubJobs].JobNo
= [Jobs].JobNo) AND ([SubJobs].ClientNo = [Jobs].ClientNo) AND
([SubJobs].IndustryNo = [Jobs].IndustryNo)"
strSQL = strSQL & " WHERE ((([SubJobs].Status) = -1))"

The error is at the following:

IIf(Not IsNull([SubJobName]) Or

[SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status

Can you have IIF statement in the query expression? because it said "Syntax
error in string in the above query expression".

Thank you in advance

Hi,
add the line
Debug.Print strSQL
just above the line
Me!ListBox_Jobs.RowSource = strSQL

run the form
press Ctl + G to open the immediate window and see what access has for
strSQL
Copy and paste strSQL into the SQL view of a new query.
Switch to datasheet view of the query, which will show you the errors in the
query.
When you get the SQL correct, the listbox should show the list of items.

you don't need the line
Me!ListBox_Jobs.Requery

and I can't see why you would need the line
Me.Refresh

Jeanette Cunningham

Hi,

i have a problem to ask.

I have the following codes in the form

Private Sub Form_Load()
strSQL = "SELECT [Jobs].JobID, [SubJobs].IndustryNo,
[SubJobs].ClientNo,
[SubJobs].JobNo, [SubJobs].SubJobNo, IIf(Not IsNull([SubJobName]) Or
[SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status"
strSQL = strSQL & " FROM [SubJobs] INNER JOIN [Jobs] ON
([SubJobs].JobNo
= [Jobs].JobNo) AND ([SubJobs].ClientNo = [Jobs].ClientNo) AND
([SubJobs].IndustryNo = [Jobs].IndustryNo)"
strSQL = strSQL & " WHERE ((([SubJobs].Status) = -1))"

Me!ListBox_Jobs.RowSource = strSQL
Me!ListBox_Jobs.Requery

Me.Refresh
End Sub

The list-box is empty when the form is loaded. I must've done something
wrong here. Could anyone help me out?

Thank you in advance

 

[Jeanette Cunningham]

You could also delete this bit from the query you are testing it on.
Now open the query in design view and write the IIf statement as a
calculated field
Expr1:IIf(Not IsNull([SubJobName]) Or
[SubJobName]<>"",[SubJobName],[JobName])
switch to sql view and see how access writes the sql for it.

You may need to put brackets around the IIf statement like this:

(IIf(Not IsNull([SubJobName]) Or [SubJobName]<>"",[SubJobName],[JobName]))
AS Expr1

Jeanette Cunningham

Thank you Jeanette for your reply.

Yes, i did as you suggested.

strSQL = "SELECT [Jobs].JobID, [SubJobs].IndustryNo,
[SubJobs].ClientNo,
[SubJobs].JobNo, [SubJobs].SubJobNo, IIf(Not IsNull([SubJobName]) Or
[SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status"
strSQL = strSQL & " FROM [SubJobs] INNER JOIN [Jobs] ON
([SubJobs].JobNo
= [Jobs].JobNo) AND ([SubJobs].ClientNo = [Jobs].ClientNo) AND
([SubJobs].IndustryNo = [Jobs].IndustryNo)"
strSQL = strSQL & " WHERE ((([SubJobs].Status) = -1))"

The error is at the following:

IIf(Not IsNull([SubJobName]) Or

[SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status

Can you have IIF statement in the query expression? because it said
"Syntax
error in string in the above query expression".

Thank you in advance

Hi,
add the line
Debug.Print strSQL
just above the line
Me!ListBox_Jobs.RowSource = strSQL

run the form
press Ctl + G to open the immediate window and see what access has for
strSQL
Copy and paste strSQL into the SQL view of a new query.
Switch to datasheet view of the query, which will show you the errors in
the
query.
When you get the SQL correct, the listbox should show the list of items.

you don't need the line
Me!ListBox_Jobs.Requery

and I can't see why you would need the line
Me.Refresh

Jeanette Cunningham

Hi,

i have a problem to ask.

I have the following codes in the form

Private Sub Form_Load()
strSQL = "SELECT [Jobs].JobID, [SubJobs].IndustryNo,
[SubJobs].ClientNo,
[SubJobs].JobNo, [SubJobs].SubJobNo, IIf(Not IsNull([SubJobName]) Or
[SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status"
strSQL = strSQL & " FROM [SubJobs] INNER JOIN [Jobs] ON
([SubJobs].JobNo
= [Jobs].JobNo) AND ([SubJobs].ClientNo = [Jobs].ClientNo) AND
([SubJobs].IndustryNo = [Jobs].IndustryNo)"
strSQL = strSQL & " WHERE ((([SubJobs].Status) = -1))"

Me!ListBox_Jobs.RowSource = strSQL
Me!ListBox_Jobs.Requery

Me.Refresh
End Sub

The list-box is empty when the form is loaded. I must've done something
wrong here. Could anyone help me out?

Thank you in advance