Problem with critera on report (1 Viewer)

amir0914

Registered User.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
151
Hi all, I wrote the blew code to create report based on "age" and "career" , the code is working right when the text boxes is filled but it gives blank report (without any records) when Text57 and Text74 is empty and I want to consider all ages when text boxes of age is empty, for ths reason I used Nz function in text boxes but I guess it's wrong :

Code:
DoCmd.OpenReport "rpt_one", acViewPreview, , "[T_age]  Between  '" & Nz(Me.Text74, "*") & "' And '" & Nz(Me.Text57, "*") & "' and  [T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"

Can someone give me a solution?

thanks in advanced.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:36
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try it this way.
Code:
DoCmd.OpenReport "rpt_one", acViewPreview, , _
     "[T_age]  Between  " & Nz(Me.Text74, 0) & " And " & Nz(Me.Text57, 999) _
    & " and  [T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"
That's assuming [T_age] is a number data type.
 

nhorton79

Registered User.
Local time
Today, 19:36
Joined
Aug 17, 2015
Messages
147
You could try:

Code:
If IsNull(Me.Text74) or IsNull(Me.Text57) then

DoCmd.OpenReport "rpt_one", acViewPreview, , "[T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"

Else

DoCmd.OpenReport "rpt_one", acViewPreview, , "[T_age] Between '" & Nz(Me.Text74, "*") & "' And '" & Nz(Me.Text57, "*") & "' and [T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"

End If

Basically check whether either of the textboxes is null and if so, don’t filter the report by date?


Sent from my iPhone using Tapatalk
 

nhorton79

Registered User.
Local time
Today, 19:36
Joined
Aug 17, 2015
Messages
147
I’m sure someone else will say it too, but you should use more meaningful names for your textboxes.

I.e. txtStartDate and txtEndDate

Will help code readability later when you come back in a couple of months/years and wonder what the hell the code is doing.


Sent from my iPhone using Tapatalk
 

Cronk

Registered User.
Local time
Today, 17:36
Joined
Jul 4, 2013
Messages
2,770
If the above responses don't solve the issue, insert the following line above the docmd.openreport
Code:
debug.print "[T_age]  Between  '" & Nz(Me.Text74, 0) & "' And '" & Nz(Me.Text57, 0) & "' and  [T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"
and see what the output is in the immediate window.


If T_Age is numeric, lose the single quotes.


Incidentally, you could replace the 'LIKE' with an '=' since there is no wild card being used. Or is that the issue that you don't have an exact match to the text in Combo12?
 

amir0914

Registered User.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
151
Hi. You could try it this way.
Code:
DoCmd.OpenReport "rpt_one", acViewPreview, , _
     "[T_age]  Between  " & Nz(Me.Text74, 0) & " And " & Nz(Me.Text57, 999) _
    & " and  [T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"
That's assuming [T_age] is a number data type.

Thank you buddy, your code is great and it works well but I have some records in table that age field is empty or not typed and your code didn't show them because it show only between 0 and 999 on report. have you any solution fr this problem?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:36
Joined
Oct 29, 2018
Messages
21,358
Thank you buddy, your code is great and it works well but I have some records in table that age field is empty or not typed and your code didn't show them because it show only between 0 and 999 on report. have you any solution fr this problem?
There are other solutions, but try it this way too:
Code:
...Nz([T_age],0]) Between...
 

amir0914

Registered User.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
151
You could try:

Code:
If IsNull(Me.Text74) or IsNull(Me.Text57) then

DoCmd.OpenReport "rpt_one", acViewPreview, , "[T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"

Else

DoCmd.OpenReport "rpt_one", acViewPreview, , "[T_age] Between '" & Nz(Me.Text74, "*") & "' And '" & Nz(Me.Text57, "*") & "' and [T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"

End If

Basically check whether either of the textboxes is null and if so, don’t filter the report by date?


Sent from my iPhone using Tapatalk

Thanks nhorton79 for your code but I already knew this way and I used it before and I'm looking for a way to do it without IF and Elseif.

you are right, I have to use appropriate names to text boxes, I will fix it next time.
 

amir0914

Registered User.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
151
If the above responses don't solve the issue, insert the following line above the docmd.openreport
Code:
debug.print "[T_age]  Between  '" & Nz(Me.Text74, 0) & "' And '" & Nz(Me.Text57, 0) & "' and  [T_career] LIKE '" & Nz(Me.Combo12, "*") & "'"
and see what the output is in the immediate window.


If T_Age is numeric, lose the single quotes.


Incidentally, you could replace the 'LIKE' with an '=' since there is no wild card being used. Or is that the issue that you don't have an exact match to the text in Combo12?

Thanks Cronk, but it still doesn't work. T_Age is string field.
 

amir0914

Registered User.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
151
Thank you very much buddy, the problem is resolved by theDBguy way. thanks to all friends.

DoCmd.OpenReport "rpt_one", acViewPreview, , " Nz([T_age],0) Between '" & Nz(Me.Text74, 0) & "' And '" & Nz(Me.Text57, 999) & "' and [T_career] = '" & Nz(Me.Combo12, "*") & "'"
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:36
Joined
Oct 29, 2018
Messages
21,358
Thank you very much buddy, the problem is resolved by theDBguy way. thanks to all friends.
Hi. Glad to hear you got it to work. We were all happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom