Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-07-2019, 11:09 AM   #1
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 42
Thanks: 49
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Problem with critera on report

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.

amir0914 is offline   Reply With Quote
Old 11-07-2019, 11:15 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Problem with critera on report

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
amir0914 (11-07-2019)
Old 11-07-2019, 11:17 AM   #3
nhorton79
Newly Registered User
 
Join Date: Aug 2015
Posts: 108
Thanks: 12
Thanked 13 Times in 13 Posts
nhorton79 is on a distinguished road
Re: Problem with critera on report

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 is offline   Reply With Quote
The Following User Says Thank You to nhorton79 For This Useful Post:
amir0914 (11-07-2019)
Old 11-07-2019, 11:21 AM   #4
nhorton79
Newly Registered User
 
Join Date: Aug 2015
Posts: 108
Thanks: 12
Thanked 13 Times in 13 Posts
nhorton79 is on a distinguished road
Re: Problem with critera on report

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
nhorton79 is offline   Reply With Quote
The Following User Says Thank You to nhorton79 For This Useful Post:
amir0914 (11-07-2019)
Old 11-07-2019, 11:31 AM   #5
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Problem with critera on report

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?
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
amir0914 (11-07-2019)
Old 11-07-2019, 11:47 AM   #6
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 42
Thanks: 49
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Re: Problem with critera on report

Quote:
Originally Posted by theDBguy View Post
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?
amir0914 is offline   Reply With Quote
Old 11-07-2019, 11:49 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Problem with critera on report

Quote:
Originally Posted by amir0914 View Post
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...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
amir0914 (11-07-2019)
Old 11-07-2019, 11:56 AM   #8
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 42
Thanks: 49
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Re: Problem with critera on report

Quote:
Originally Posted by nhorton79 View Post
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 is offline   Reply With Quote
Old 11-07-2019, 11:59 AM   #9
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 42
Thanks: 49
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Re: Problem with critera on report

Quote:
Originally Posted by Cronk View Post
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 is offline   Reply With Quote
Old 11-07-2019, 12:43 PM   #10
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 42
Thanks: 49
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Re: Problem with critera on report

Thank you very much buddy, the problem is resolved by theDBguy way. thanks to all friends.

Quote:
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, "*") & "'"
amir0914 is offline   Reply With Quote
Old 11-07-2019, 12:50 PM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Problem with critera on report

Quote:
Originally Posted by amir0914 View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
amir0914 (11-07-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 08:02 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World