Form Textbox based on a Query Criteria

[Peace]

Hi everyone.. Can someone help me please? In Access form I have two textboxes (Months From) and (Months To). These two textboxes returns records based on the following query criteria code:

Between [Forms]![frm_JobPlacementInfo]![MonthFrom TextBox] And [Forms]![frm_JobPlacementInfo]![MonthTo TextBox]

So, if for instance I put 3 in the first textbox and 9 in the second textbox, then I will get all records containing the numbers between 3 and 9. This works perfect and great.
My problem is: when I want to keep the two textboxes blank to get all records, the query fails to give all available records because I did not put any number in the textboxes, which means no record matches my criteria.


I adjusted my code to the following:

Between [Forms]![frm_JobPlacementInfo]![MonthFrom TextBox] And [Forms]![frm_JobPlacementInfo]![MonthTo TextBox] Or Like [Forms]![frm_JobPlacementInfo]![MonthFrom TextBox] & "*"

This code solves the previous problem, but I got a new problem. When I put the numbers 3 and 9 in the textboxes, I am not getting only records have numbers between 3 and 9 only but also records have numbers like 13, 30, 33, 19, 90, or 99.
I got confused and don’t know how to solve this new problem. Is there any suggestion please?
Thank you so m much.

[June7]

Try:

Between Nz([Forms]![frm_JobPlacementInfo]![MonthFrom TextBox],0) And Nz([Forms]![frm_JobPlacementInfo]![MonthTo TextBox],99999)

[Peace]

Thank you so much June7. This worked perfect. I have similar textboxes in the same form but to get (Start Date) and (End Date). I have the following code which works well:

Between [Forms]![frm_JobPlacementInfo]![StartDateTextBox] And [Forms]![frm_JobPlacementInfo]![EndDate TextBox] Or Like [Forms]![frm_JobPlacementInfo]![StartDateTextBox] & "*"

Do you think it is better to use your code in this situation to avoid any mistakes. For example, should I replace the above code with the follwoing one:

Between NZ([Forms]![frm_JobPlacementInfo]![StartDateTextBox],#1/1/1900#) And NZ([Forms]![frm_JobPlacementInfo]![EndDate TextBox],#12/31/2020#)

Do you have any alternative codes/suggestions.. Thank you so much June7, with my best wishes.

[June7]

With LIKE and wildcard data is treated as text, even if only numbers are involved.

Which is why the match on full date works but matching on month only was not satisfactory.

Use whichever makes you more comfortable.

[Peace]

Got it. Thank you June7. I hightly appreciate your answers and feedback.