WHERE statement with 2 criteria and involving Dates (1 Viewer)

[Apples241]

Hello,

I have a BeginDate field and an EndDate field on my form that filters via a command button the data on the form to just the dates within the range entered by the user. I just have a form, frmNetworkPerformance (no subform), based on a table, tblNetworkPerformance.

The code in the On Click for the command button works below, but I need to add a line that if either of the dates do not exists, then a msgbox will appear saying, "BeginDate (or EndDate, whichever is applicable) Not Found" (I'm not working on this quite yet, I will have a variable called strDate, I think):

Private Sub cmdNtwkFilterByDate_Click()

    With Me.Form
        .Filter = "[DateNetwork] BETWEEN #" & Me.BeginDate & "# AND #" &   Me.EndDate & "#"
        .FilterOn = True
    End With
    End If

End Sub

I need help with the 2 date fields as criteria on the WHERE statement. This is what I have tried but I'm having trouble with having 2 criteria for the WHERE and am getting lost with the apostrophes for the BeginDate and EndDate. I need the (space) and underscore in the code before the OR b/c the line of code is too long. (I haven't gotten to the part where I put strDate = to something; I'll work on that later.

With just 1 criteria after the WHERE, I get "Run-time error '3061'. Too few parameters. Expected 1." I know it has to do with quotes but I've not worked with Recordset and Dates before.

With both criteria as I have posted below, the first line of code with the IF and SQL statement are red and the macro recorder opens up but it is empty.

I would appreciate any help!

Private Sub cmdNtwkFilterByDate_Click()

Dim strDate as Date

    If CurrentDb.OpenRecordset("SELECT DateNetwork FROM tblNetworkPerformance WHERE BeginDate<>" & "#" & Forms!frmNetworkPerformance!BeginDate & "#" _
OR EndDate<>" & "#" & Forms!frmNetworkPerformance!EndDate & "#"& ";", dbOpenSnapshot) Then
        MsgBox (strDate &" Not Found")
    Else
       With Me.Form
        .Filter = "[DateNetwork] BETWEEN #" & Me.BeginDate & "# AND #" & Me.EndDate & "#"
        .FilterOn = True
    End With
    End If

End Sub

 

[jdraw]

You might try something along this logic (untested)

if not isdate(beginDate)  then
   MsgBox " BeginDate missing msg here"
ElseIf Not isdate(EndDate) then
   MsgBox "EndDate missing msg here"
End if

 

[Apples241]

Thank you, jdraw. I tested it and it will still filter the form b/c I think IsDate is seeing if there is a date in the text box, which there is, but I need a msgbox if a user puts in a date that does not exist in the underlying table of the form.

For ex: if the table only has dates of 2/1/2017, and a user puts and Begin Date of 2/1/2017 and End Date of 2/2/2017, then the msgbox would pop up b/c the End Date of 2/2 does not exist in the table. Or if the Begin Date was 2/2/2017 and End Date 2/3/2017, where the Begin Date is not in the table, etc.

I would appreciate any help for you or anyone else. Thank you, again.

 

[jdraw]

The suggested logic should include Me. before BeginDate and EndDate or whatever your text boxes on the form are named.

Can you show the code you used that isn't working?

 

[Cronk]

CurrentDb.OpenRecordset is not a function and will not return true or false depending on if there are records in the recordset.

You need to declare a recordset eg

dim rst as recordset

Then open the recordset and test if there are any records

set rst = currentdb.openrecordset(".....
if rst.recordcount <1 then
   '-- No records matching criteria
else
   '--apply filter
endif

You could also use DLookup to test if records exist that match the criteria

 

[Apples241]

The suggested logic should include Me. before BeginDate and EndDate or whatever your text boxes on the form are named.

Can you show the code you used that isn't working?

jdraw,

I've changed the code now to Cronk's suggestion and I get a different error, so let me list two issues below:

1) The 1st error you are asking about is on the line below. I am confused on having two criteria for a WHERE statement, and I am lost on how to use the quotes.

With just 1 criteria after the WHERE, I get "Run-time error '3061'. Too few parameters. Expected 1." I know it has to do with quotes but I've not worked with Recordset and Dates before.

With both criteria as I have posted below, the first line of code with the IF and SQL statement are red and the macro recorder opens up but it is empty.

If CurrentDb.OpenRecordset("SELECT DateNetwork FROM tblNetworkPerformance WHERE BeginDate<>" & "#" & Forms!frmNetworkPerformance!BeginDate & "#" _
OR EndDate<>" & "#" & Forms!frmNetworkPerformance!EndDate & "#"& ";", dbOpenSnapshot)

2) Using Cronk's code, I now get a 'Compile error - Type Mismatch' where the

.OpenRecorset

part of

Set rst = CurrentDb.OpenRecordset

is highlighted.

Here is my new full code based on Cronk's post:

Private Sub cmdNtwkFilterByDate_Click()

    Dim rst As Recordset
    
    Set rst = CurrentDb.OpenRecordset("SELECT DateNetwork FROM tblNetworkPerformance WHERE BeginDate<>" & "#" & Forms!frmNetworkPerformance!BeginDate & "#") & _
    ("OR EndDate<>" & "#" & Forms!frmNetworkPerformance!EndDate & "#")
     
     If rst.RecordCount < 1 Then
        MsgBox "No records match that criteria", vbCritical, "No records found"
     Else
         With Me.Form
            .Filter = "[DateNetwork] BETWEEN #" & Me.BeginDate & "# AND #" & Me.EndDate & "#"
            .FilterOn = True
         End With
End Sub

I'm new to Access VBA, so is there a reference I need to add for this error? I can't access References under Tools b/c it is greyed out.

Thank you both for your help! Can you all or anyone help?

 

[Apples241]

CurrentDb.OpenRecordset is not a function and will not return true or false depending on if there are records in the recordset.

You need to declare a recordset eg

dim rst as recordset

Then open the recordset and test if there are any records

set rst = currentdb.openrecordset(".....
if rst.recordcount <1 then
   '-- No records matching criteria
else
   '--apply filter
endif

You could also use DLookup to test if records exist that match the criteria

Cronk, thank you very much for your reply. Would you take a look at my response to jdraw? I would like you to see my new code, based on yours, and see the new Compile error I'm getting, if you don't mind. Thank you, again.

 

[Minty]

Your SQL String isn't quite right - and I've added some bits to clarify it hopefully.

Private Sub cmdNtwkFilterByDate_Click()

    Dim rst As Recordset
    Dim sSql as string
    
    sSql = "SELECT DateNetwork FROM tblNetworkPerformance "
    sSql = sSql & "WHERE BeginDate [COLOR="Red"]<>[/COLOR] #" & Forms!frmNetworkPerformance!BeginDate & "# OR EndDate [COLOR="red"]<>[/COLOR] #" & Forms!frmNetworkPerformance!EndDate & "# ;"
    	
    Debug.print sSql   
    
    Set rst = CurrentDb.OpenRecordset(sSql, dbOpenSnapShot)
     If rst.RecordCount < 1 Then
        MsgBox "No records match that criteria", vbCritical, "No records found"
     Else
         With Me.Form
            .Filter = "[DateNetwork] BETWEEN #" & Me.BeginDate & "# AND #" & Me.EndDate & "#"
            .FilterOn = True
         End With
     End If

End Sub

However I suspect the <> comparisons in red aren't quite what you are trying to achieve ?

 

[static]

Private Sub cmdNtwkFilterByDate_Click()

	sql = "SELECT 1 FROM tblNetworkPerformance WHERE "
	
	sd = not CurrentDb.OpenRecordset(sql & "BeginDate=#" & format(BeginDate,"dd-mmm-yyyy") & "#").eof
	ed = not CurrentDb.OpenRecordset(sql & "EndDate=#" & format(EndDate,"dd-mmm-yyyy") & "#").eof
	
	msg = IIf(sd And ed, "", IIf(Not sd And Not ed, "Start and end dates  are missing", _
			IIf(Not sd, "start date is missing", "End date is missing")))
			
	if msg = "" then
		me.Filter = "[DateNetwork] BETWEEN #" & BeginDate & "# AND #" & EndDate & "#"
		me.FilterOn = True
        else
               msgbox msg
        End If

End Sub

 

[jdraw]

Apples241,

For ex: if the table only has dates of 2/1/2017, and a user puts and Begin Date of 2/1/2017 and End Date of 2/2/2017, then the msgbox would pop up b/c the End Date of 2/2 does not exist in the table. Or if the Begin Date was 2/2/2017 and End Date 2/3/2017, where the Begin Date is not in the table, etc.

Usually you have textboxes on a form to select the BeginDate and EndDate of a range you are interested in. You use those values to find records in a table/query that have dates with that range.
You are not usually looking for a specific date in a table/query to match your textbox value.

You specifically used BETWEEN which implies a range. If you were looking for a specific date in your table/query I would suggest you use "=".

If you want to check that the user has supplied values for BeginDate control on the form. you could do the IsDate check in the beginning of your Click event, same with a check on EndDate.

Maybe I'm still not clear about your requirement.

 

[Minty]

+1 For what Plog is saying - I put default dates in to most of my Date criteria forms. Normally I set the default FromDate to the beginning of the current month and EndDate to the current date.

 

[Apples241]

Your SQL String isn't quite right - and I've added some bits to clarify it hopefully.

Private Sub cmdNtwkFilterByDate_Click()

    Dim rst As Recordset
    Dim sSql as string
    
    sSql = "SELECT DateNetwork FROM tblNetworkPerformance "
    sSql = sSql & "WHERE BeginDate [COLOR="Red"]<>[/COLOR] #" & Forms!frmNetworkPerformance!BeginDate & "# OR EndDate [COLOR="red"]<>[/COLOR] #" & Forms!frmNetworkPerformance!EndDate & "# ;"
    	
    Debug.print sSql   
    
    Set rst = CurrentDb.OpenRecordset(sSql, dbOpenSnapShot)
     If rst.RecordCount < 1 Then
        MsgBox "No records match that criteria", vbCritical, "No records found"
     Else
         With Me.Form
            .Filter = "[DateNetwork] BETWEEN #" & Me.BeginDate & "# AND #" & Me.EndDate & "#"
            .FilterOn = True
         End With
     End If

End Sub

However I suspect the <> comparisons in red aren't quite what you are trying to achieve ?

Minty, thank you for your reply. I'm trying yours and static's code and I'm getting similar errors.

For your code I left everything as you wrote it, except changed the dbOpenSnapshot to dbOpenDynaSet and the <> to = in the WHERE statement.

The error I get is Run Time error 3061 - Too few parameters, Expected 2 and it highlights

Set rst = CurrentDb.OpenRecordset (sSQL, dbOpenDynaset)

.

I don't know why that is a problem b/c I looked up 'OpenRecordset' for Access VBA and all of the parameters are optional except for the first one, the sSQL part.

With statics, I get the same error except it is expected 1, Run Time error 3061 - Too few parameters, Expected 1. I just changed the date to mm-dd-yyyy.

I tried adding dbOpenDynaset

sd = not CurrentDb.OpenRecordset(sql & "BeginDate=#" & format(BeginDate,"dd-mmm-yyyy") & "#", dbOpenDynaset).eof

, but I still get Expected 1 error.

I then added Dim statements as below b/c a site said that 'Unfortunately, the db engine doesn't know anything about that variable. So, when it sees (the field name), it presumes that is the name of a parameter for which you haven't supplied a value.'

Dim sd As Recordset
Dim ed As Recordset
Dim SQL As String
Dim BeginDate As TextBox
Dim EndDate As TextBox

When I use Dim statements, the error is 'Compile error: Invalid use of property' and it highlights just the

sd=

in

 sd = Not CurrentDb.OpenRecordset(SQL & "BeginDate=#" & Format(BeginDate, "mm-dd-yyyy") & "#", dbOpenDynaset).EOF

. Can either of you or nayone help with this b/c it is so close? (I was going to post another code but these are closer to what I need, I think.)

Thank you!

 

[Apples241]

Private Sub cmdNtwkFilterByDate_Click()

	sql = "SELECT 1 FROM tblNetworkPerformance WHERE "
	
	sd = not CurrentDb.OpenRecordset(sql & "BeginDate=#" & format(BeginDate,"dd-mmm-yyyy") & "#").eof
	ed = not CurrentDb.OpenRecordset(sql & "EndDate=#" & format(EndDate,"dd-mmm-yyyy") & "#").eof
	
	msg = IIf(sd And ed, "", IIf(Not sd And Not ed, "Start and end dates  are missing", _
			IIf(Not sd, "start date is missing", "End date is missing")))
			
	if msg = "" then
		me.Filter = "[DateNetwork] BETWEEN #" & BeginDate & "# AND #" & EndDate & "#"
		me.FilterOn = True
        else
               msgbox msg
        End If

End Sub

static, thank you. Can you take a look at my reply to Minty b/c I get Run time 3061 error Too few Parameters for both codes, but yours is Expected 1. I thought OpenRecordset had optional parameters to it except for the first one.

I made adjustments per my reply to Minty but I still get that error or another Compile error if I add Dim statements to your code. Can you or anyone help b/c I think both of these codes will work?

I appreciate any help. I will keep researching.

 

[jdraw]

Apples,

Can you post a copy of your database in zip format? Remove/change anything confidential first

 

[static]

try

Private Sub cmdNtwkFilterByDate_Click()

    sql = "SELECT 1 FROM tblNetworkPerformance WHERE "
    
    sd = not CurrentDb.OpenRecordset(sql & "DateNetwork=#" & format(BeginDate,"dd-mmm-yyyy") & "#").eof
    ed = not CurrentDb.OpenRecordset(sql & "DateNetwork=#" & format(EndDate,"dd-mmm-yyyy") & "#").eof
    
    msg = IIf(sd And ed, "", IIf(Not sd And Not ed, "Start and end dates  are missing", _
            IIf(Not sd, "start date is missing", "End date is missing")))
            
    if msg = "" then
        me.Filter = "DateNetwork BETWEEN #" & BeginDate & "# AND #" & EndDate & "#"
        me.FilterOn = True
    else
        msgbox msg
    End If

End Sub

 

[Minty]

Okay - I changed it to a snapshot as you only need to read data, you have no need to open it for editing, it's simply more efficient.

When it highlights the error can you please bring up the immediate window in the VBA editor by pressing ctrl + g - you should see the text of the sSql string - please post that up here. You may need to press the code break (stop button).

 

[Apples241]

Okay - I changed it to a snapshot as you only need to read data, you have no need to open it for editing, it's simply more efficient.

When it highlights the error can you please bring up the immediate window in the VBA editor by pressing ctrl + g - you should see the text of the sSql string - please post that up here. You may need to press the code break (stop button).

Hello Minty,

This is the sSql line in the immediate window.
SELECT DateNetwork FROM tblNetworkPerformance WHERE BeginDate = #2/1/2017# OR EndDate = #2/1/2017# ;

But here is the issue, after I looked at statics code, it's DateNetwork =, etc.:

 sSql = sSql & "WHERE DateNetwork = #" & Forms!frmNetworkPerformance!BeginDate & "# OR DateNetwork = #" & Forms!frmNetworkPerformance!EndDate & "# ;"

It works, now. Statics works, as well. I can use either code. I will need to incorporate statics msgbox lines, the IIF, to yours where if you have a wrong Begin or End Date, you will get an error msg. Thank you so much for your help! I really appreciate your patience and time.

Edit: P.S. I tried adding to your and static's reputation but I could only add to the other's how replied. It says that I have to spread reputations around more; otherwise, I would add to your reputation.

 

[Apples241]

try

Private Sub cmdNtwkFilterByDate_Click()

    sql = "SELECT 1 FROM tblNetworkPerformance WHERE "
    
    sd = not CurrentDb.OpenRecordset(sql & "DateNetwork=#" & format(BeginDate,"dd-mmm-yyyy") & "#").eof
    ed = not CurrentDb.OpenRecordset(sql & "DateNetwork=#" & format(EndDate,"dd-mmm-yyyy") & "#").eof
    
    msg = IIf(sd And ed, "", IIf(Not sd And Not ed, "Start and end dates  are missing", _
            IIf(Not sd, "start date is missing", "End date is missing")))
            
    if msg = "" then
        me.Filter = "DateNetwork BETWEEN #" & BeginDate & "# AND #" & EndDate & "#"
        me.FilterOn = True
    else
        msgbox msg
    End If

End Sub

static, thank you so much. It works! I can't believe I didn't catch the change you made with DateNetwork. Thank you also for the flexibility with the msgbox so that if there is missing info in the Begin or End date or both, it will give a msgbox.

Thank you very much, again, for your patience and time, as well.

Edit: P.S. I tried adding to your and Minty's reputation but I could only add to the other's how replied. It says that I have to spread reputations around more; otherwise, I would add to your reputation.

 

[Apples241]

Apples,

Can you post a copy of your database in zip format? Remove/change anything confidential first

jdraw, I just finished getting the db ready to upload when I saw the two replies from Minty and static. Their code works!

Thank you so much for your help and willingness to upload the db. Thanks, again!

 

[Apples241]

Minty and static, the forum won't let me add to your reputation b/c it says I have to spread it around. I added to everyone's reputation on this thread, so maybe it is b/c I have added to your reputation before. Otherwise, I would add to your reputations. Thank you, again.