I have an unbound form that holds a combo box, two unbound text boxes, and a list box. The combo box populates the list box, along with the text boxes which are used as start and end dates for the data displayed in the list box which is based off of the following query:

SELECT qryEntityTransactions.TransactionID, qryEntityTransactions._
TransactionDate, qryEntityTransactions.PaidTo, qryEntityTransactions.PaidBy,_
qryEntityTransactions.AmountDesctiption, qryEntityTransactions.Value
FROM qryEntityTransactions
WHERE (((qryEntityTransactions.TransactionDate)_
>=[Forms]![frmTransactionLookup]![txtStartDate] And(qryEntityTransactions.TransactionDate)_
<=[Forms]![frmTransactionLookup]![txtEndDate]))
ORDER BY qryEntityTransactions.TransactionDate, qryEntityTransactions.AmountDesctiption;

Now, I deal with codes in design view normally, as I know very little about SQL. What I want, is if the start and end date text boxes are null it will show me all the records for what is selected in the combo box, as opposed to the entered dates.

Would I try to include and if then clause in the query? Or would I try to produce some code on the "current" event of the form to remove the date filter from the query? OR should I write code to filter by the date instead of using it in the query and then add the code for the null possibilities?

I would not be opposed to actual answers, but am really just asking to be pointed in the right direction, as I do want to learn what code to write, and not just copy and paste something.

I would break up the sql into two parts....first part the select part and the where part. Then, check for Null. If null, talior the where part to include all records...if not null, build the where clause to include the date range.

Lastly, put the two together, add the Order by and the sql is built.

I assuming you have a query that tests the date, and therefore you have something like this in the date criteria.

between forms!myform!startdatefield and forms!myform!enddatefield

I tend to do this with functions

these must be in a code module

Have a global variable called startdate
if your field is null, then set the global startdate to 0 (see the functions below)

Have a global variable called enddate
if your field is null, then set the global enddate to 0 (see the functions below)


you have to set these at a suitable point in your code - either in after update events, or in your prepare reprot button click


have global functions (these must also be in the code module)

function readstartdate as date
'you need the nz here in the next line - otherwise if the gblvariable is not initiallised you get a runtime error
'the function must return a date - if its 0, its the lowest date the system can have, therefore it will be lower than any true date in your system
readstartdate= nz(gblstartdate,0)
end function

function readenddate as date
'now we need to make sure a null date is HIGHER than any date in your system
if nz(gblenddate,0) = 0 then gblenddate = #12/31/2099#
readenddate= gblenddate
end function

---------------
now in your criteria you can just say

between readstartdate and readenddate, and if the selection range is blank you can get everything

you can also use these dates and date functions anywhere in your project.

note that you have to use the functions in the query, as you cannot refer to a variable directly in a query (although you can refer to a field on a form, and obviously you can refer to the variables directly in your code)


possibly sounds tricky but its dead easy - just do it once, you'll see

I would break up the sql into two parts....first part the select part and the where part. Then, check for Null. If null, talior the where part to include all records...if not null, build the where clause to include the date range.

Lastly, put the two together, add the Order by and the sql is built.

I have been mulling over how to do this (Which is why I haven't repsponded sooner), and I must say I am stumped. I just plain don't know enough. So much for trying to do this myself..... I can't wait until I have more time to study the books I bought on vba. Thanks for your input! It is greatly appreciated.

possibly sounds tricky but its dead easy - just do it once, you'll see

It does sound tricky, but I will try it. Heading home now, May not get to it until Monday. I'll let you know if I have sucess or questions! Thank you so much and have a wonderful weekend!

Two Observations:
I prefer to use the Between Command in a situation like this one.
If you know the minimum value for qryEntityTransactions.TransactionDate, then you can use an IIf() statement to resolve the issue (Something like the Where Clause displayed below).

WHERE qryEntityTransactions.TransactionDate Between
IIf(([Forms]![frmTransactionLookup]![txtStartDate] Is Null),
{MinimumDateValue},
[Forms]![frmTransactionLookup]![txtStartDate]) And
IIf(([Forms]![frmTransactionLookup]![txtEndDate] Is Null),
Date(),
[Forms]![frmTransactionLookup]![txtEndDate])

I have been mulling over how to do this (Which is why I haven't repsponded sooner), and I must say I am stumped. I just plain don't know enough. So much for trying to do this myself..... I can't wait until I have more time to study the books I bought on vba. Thanks for your input! It is greatly appreciated.

Here is an example from the main search box in the database here where I work.


stSearchField = Me.cboSearchChoice
stSearchCriteria = Me.txtSearchBox

stBaseSql = "SELECT tblCustomers.CUSTOMER_ID, [firstname] & " & Chr(34) & " " & Chr(34) & _
" & [middleinitial] & " & Chr(34) & " " & Chr(34) & " & [lastname] AS FullName, tblCustomers.PHONE_NUMB, " & _
"tblCustomers.BILL_TO_AD "
Me.txtSearchStatus = "SEARCHING..."

Select Case stSearchField
Case 1 'phone number search
stWhereClause = " FROM tblCustomers WHERE (((tblCustomers.PHONE_NUMB)" & _
"=" & Chr(34) & stSearchCriteria & Chr(34) & "));"

stCombinedSql = stBaseSql & " " & stWhereClause

Case 2 ' last name search
If Me.fraSearchType = 1 Then 'with wild card
stWhereClause = ", tblCustomers.LastName FROM tblCustomers WHERE" & _
"(((tblCustomers.LastName) Like " & Chr(34) & stSearchCriteria & "*" & Chr(34) & "));"
End If

If Me.fraSearchType = 2 Then 'without wild card
stWhereClause = ", tblCustomers.LastName FROM tblCustomers " & _
"WHERE (((tblCustomers.LastName)=" & Chr(34) & stSearchCriteria & Chr(34) & "));"
End If

stCombinedSql = stBaseSql & stWhereClause

'More Case Select option....

'After Case select....

Me.lstCustInfo.RowSource = stCombinedSql
Me.lstCustInfo.Requery
Me.txtSearchStatus = "SEARCH COMPLETE"



Basically, the case select is based on a combo box which list the different search fields. A number is tied to each selection. Hope it makes sense...if you need me to, I can post all the code behind the search button if you would like.

As part of the learning exercise perhaps you would like to read this thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Also this issue has been discussed on other threads.

Brian

Hi All, I have worked on this problem, trying to use the global variable route, as I wanted to learn how to do it. I almost have it working. The only error I get now is in the txtStartDate. When the form is opened and both date boxes are null, it displays the records like I want them too. When I put dates into the boxes the filter works, even if I leave the startdate null. If, however, I have a start date, then take that out I get an error (See GIF. - Run Time Error 94). Then if I go to Debug it pops up a line of code (See GIF. - Run Time Error 94 - Code) I thought that my global function readStartDate was supposed to take care of the null instance, so I don't understand what it is doing. Can anyone give me a better understanding of what is happening, or an explanation as to why it is throwing up this error?

I just realized that it does the same thing when I delete the end date.

Thank you all for your previous suggestions. :)

fix your error by using

If Len(Me.txtStartDate & "") > 0 Then
gblStartDate = Me.txtStartDate
Me.lstTransactionAmounts.Requery
End If

fix your error by using

If Len(Me.txtStartDate & "") > 0 Then
gblStartDate = Me.txtStartDate
Me.lstTransactionAmount.Requery
End If


Thanks for the response, I'll need to look into what "Len" would do for it. I get a new error now which I didn't before, so I don't know if something else is wrong, or if it is this code.

Compile Error: Method or data member not found

Which pops up my VBA screen and highlights my Me.lst..... line of the code (See Attached)

My Current Code for Reference:
Private Sub txtStartDate_AfterUpdate()
If Len(Me.txtStartDate & "") > 0 Then
gblStartDate = Me.txtStartDate
Me.lstTransactionAmount.Requery
End If
End Sub

Thanks for the response, I'll need to look into what "Len" would do for it. I get a new error now which I didn't before, so I don't know if something else is wrong, or if it is this code.

Compile Error: Method or data member not found

Which pops up my VBA screen and highlights my Me.lst..... line of the code (See Attached)
Looks like you're missing an S on the name in that procedure.

I'll need to look into what "Len" would do for it.
The Len function with the empty string appended will handle nulls AND empty strings.

Looks like you're missing an S on the name in that procedure.

So wierd that it didn't pop up that error originally.

So wierd that it didn't pop up that error originally.

If you look at your screenshot you will see that the S is there. It disappeared in my code, which is why it didn't have a problem before :D

The Len function with the empty string appended will handle nulls AND empty strings.

I see, so when I delete the date, it really isn't null.

OK... I feel like I'm back at square one. I get no errors, but when I delete a date and tab, my list box doesn't requery. I am left with the original filter. How do I get it to run the query again? The requery seemed to be working before, I don't understand what I've done enough to figure out why it's not requerying now.

I see, so when I delete the date, it really isn't null.

OK... I feel like I'm back at square one. I get no errors, but when I delete a date and tab, my list box doesn't requery. I am left with the original filter. How do I get it to run the query again? The requery seemed to be working before, I don't understand what I've done enough to figure out why it's not requerying now.

Oh, sorry, I guess you should move the requery outside of the If, like this, but you may have a problem with gblStartDate hanging around with the old value. If you do, we'll have to figure something else out.


If Len(Me.txtStartDate & "") > 0 Then
gblStartDate = Me.txtStartDate
End If
Me.lstTransactionAmounts.Requery

Oh, sorry, I guess you should move the requery outside of the If, like this, but you may have a problem with gblStartDate hanging around with the old value. If you do, we'll have to figure something else out.


If Len(Me.txtStartDate & "") > 0 Then
gblStartDate = Me.txtStartDate
End If
Me.lstTransactionAmounts.Requery

I did try that before I posted, wondering if that was why, and it still didn't work..... Now I know I don't know how to fix it, as this is my first time using a global, just getting it to work felt like a triumph (although very short lived :D) I really appreciate you helping me with this.

What do you have for your list box's rowsource?

What do you have for your list box's rowsource?

A Select query:

SELECT qryEntityTransactions.TransactionID, qryEntityTransactions.TransactionDate, qryEntityTransactions.PaidTo, qryEntityTransactions.PaidBy, qryEntityTransactions.AmountDesctiption, qryEntityTransactions.Value
FROM qryEntityTransactions
WHERE (((qryEntityTransactions.TransactionDate) Between readStartDate() And ReadEndDate()))
ORDER BY qryEntityTransactions.TransactionDate, qryEntityTransactions.AmountDesctiption;

Perhaps instead of using that, you should look into using code like what I have in this sample (http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip) to build your rowsource.

Bob what does the "If Not" mean in your code for the report? I haven't seen that yet in anything I've looked at. EX: If Not NZ([StartDate]) Then

Would that mean "If the start date isn't null?"

My code has

If IsNull(Me.txtFrom) And Me.txtFrom = "" Then
If Not IsNull(Me.txtTo) And Me.txtTo <> "" Then
stWhere = stWhere & "[TravelDate] <=" & Me.txtTo & "#"
blnTrim = False
End If

so yes, where the

If Not IsNull(Me.txtTo)

means that if the field is not null then...

Hi Bob and Scooterbug. I appreciate all of your help. Bob, I asked a co-worker (who has taught me so much about access especially two years ago when I was first starting) to help me understand your code. I had some idea, but wanted to run my idea of what it meant past him (To check my understanding). He also suggested that it might not work for my case. Or would be more complicated then what I was already working on (the global function thing.) So that is what I went with. I am still going to explore your option though, as one of the reasons I am messing around with this is to learn how to do this stuff.

I'll repost the database, which the form is basically working now. I just need to put in some error handling stuff so that if you type a partial date by mistake it won't go bonkers. But that does not need to be done here in this post. If I have questions I'll start a new one.

Again, Thank you for all of your help, everyone who posted suggestions, links, solutions, etc... I love this site.