Using ReplaceWhereClause with Dates

dpm1057 · September 21st, 2011, 08:46 AM

Hi, I have been trying to use the RepalceWhereClause function with a date. My Union query that selects the dates is as follows:

SELECT DISTINCT tblWorkPlans.Target_Go_Live, tblWorkPlans.Target_Go_Live
FROM tblWorkPlans
UNION SELECT #1/1/1900# , "<all>"
FROM tblWorkPlans
ORDER BY tblWorkPlans.Target_Go_Live;

The Combo box has been set up to bind column 1, have 2 columns, widths 0;1 to show only the right column.

When I choose a date or if I choose <all>, I get an error message:
25880: The record source ' WHERE tblWorkPlans.Target_Go_Live = #5/28/2012# ' specified on this form or report does not exist.

If I choose <all> I get the same message except there is nothing between the quotes. 25880: The record source ' ' specified on this form or report does not exist.

Aside from my IF statements, the code comes direct from the SelectRecords code made available for download.

Public Sub SelectRecords()
On Error GoTo Error_Handler

Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cobFilGoLive <> #1/1/1900# Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkPlans.Target_Go_Live = #" & cobFilGoLive & "#"
End If

varWhereClause = " WHERE " + varWhereClause

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)
Me.Requery

'EnableDisableControls

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
'DisplayUnexpectedError Err.Number, Err.Description
MsgBox (Err.Number & ": " & Err.Description)
' simpler version without centralized error text
Resume Exit_Procedure
Resume

End Sub

Any thoughts on what could be wrong here?

gjgriffith · September 21st, 2011, 02:47 PM

Hello dpm1057,

Thank you so much for posting your question in the "Access 2007 VBA Programmer's Reference" book forum. You asked:

Quote:

When I choose a date or if I choose <all>, I get an error message:
25880: The record source ' WHERE tblWorkPlans.Target_Go_Live = #5/28/2012# ' specified on this form or report does not exist.
...
...
...
Any thoughts on what could be wrong here?

So, I think I see the answer to your problem. You set the value for the where clause that you are going to replace to NULL in your code that you've provided here:

Code:

...
varWhereClause = Null
strAND = " AND "

If cobFilGoLive <> #1/1/1900# Then
    varWhereClause = (varWhereClause + strAND) & _
    "tblWorkPlans.Target_Go_Live = #" & cobFilGoLive & "#"
End If
...

In the above code, first you set the "varWhereClause" variable to Null and then you concatenate the Null to the " AND " clause, in the line of code that says:

Code:

    varWhereClause = (varWhereClause + strAND) & _
    "tblWorkPlans.Target_Go_Live = #" & cobFilGoLive & "#"

which would be invalid for the WHERE clause. Does that make sense? Hopefully you can understand the problem here, because it looks like you've created an invalid WHERE clause.

Also, my question to you is: Does this error appear in the Access 2007 VBA Programmer's Reference? If so, could you let me know the location in the book (i.e. the page number)? And thanks again for posting your questions!

Sincerely,

dpm1057 · September 21st, 2011, 08:35 PM

Apologies... I fat fingered the error code in my post... it's 2580 not 25880. I am using an e-book version (Kindle) and a search for the error code in the book yields no results.

dpm1057 · September 22nd, 2011, 07:56 AM

Hi Geoffrey,

Thanks for the response, but it does not make sense. Mostly because this is how the example provided in the sample database "Chamber Application" was written.

All I have done is change the 2 conditional IF statements to reflect the objects and tables I am filtering upon, and deleted the EnableDisableControls command since I am not using that.

This code works in the sample database, but it is not working when I try it with a date or text value from my database.

Public Sub SelectRecords()
On Error GoTo Error_Handler

Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cboMemberStatusKey & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblBusiness.MemberStatusKey = """ & _
cboMemberStatusKey & """"
End If

If Not IsNull(txtPaymentAmt) Then
varWhereClause = (varWhereClause + strAND) & _
"tblBusiness.BusinessKey IN (" & _
"Select BusinessKey From tblPayment Where" & _
" PaymentAmount = " & Me!txtPaymentAmt & ")"
End If

varWhereClause = " WHERE " + varWhereClause

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)
Me.Requery

EnableDisableControls

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
'MsgBox (Err.Number & ": " & Err.Description)
' simpler version without centralized error text
Resume Exit_Procedure
Resume

End Sub

gjgriffith · September 22nd, 2011, 09:19 PM

Hello dpm1057,

Ah, I think I understand now. You said:

Quote:

This code works in the sample database, but it is not working when I try it with a date or text value from my database.

Hmm, this comment makes me think there is a problem with how you are providing the WHERE clause statements. It is important to remember, in a WHERE clause:

1. Number type fields are normal. Example: [NumberField]=3
2. Text fields should be surrounded by single or double quotes. Example: [TextField]="Some Text"
3. Date fields should be surrounded by pound signs. Example: [DateField]=#09/22/2011#

Does this make sense? I'm guessing that the where clause itself is not being setup properly for each different field type.

So, hopefully that helps, but please let me know if you have any other questions. And thanks for reading the Access 2007 VBA Programmer's Reference - I really appreciate it!

Thanks,

dpm1057 · September 23rd, 2011, 08:09 AM

Hi Geoffrey,

Thanks for trying to help with this, but I am starting to think that the issue is with one of the other pieces of code. This morning I took a copy of the working code from the example database. The example had 2 IF statements, on that looked at a text based combo box and the other that looked for a price. I deleted the price statements. Then I edited the text process by copying the object names from the forms property window and pasting them over the corresponding names from the sample code.

I even toyed with using the brackets around my table and field names. I still get the Error 2580, but then is shows me a valid WHERE clause with the message.

I am starting to wonder if it is my field names and how the ParseSQL and RepalceWhereClause procedures work. Both my field names have under scores in them. INVISION_CUSTOMER_NAME or Target_Go_Live. Could those underscores be throwing the process off?

gjgriffith · September 23rd, 2011, 01:02 PM

Hello dpm1057,

Thanks for the follow up. You said:

Quote:

...but I am starting to think that the issue is with one of the other pieces of code.
...
...
Both my field names have under scores in them. INVISION_CUSTOMER_NAME or Target_Go_Live. Could those underscores be throwing the process off?

Hmm, I'm not sure what to say. Underscores are allowed in field names, so that should not be the issue.

And at this point, it sounds like the code you provided above is NOT the exact code that you are having a problem with. For example, neither of the variables "Target_Go_Live" or "INVISION_CUSTOMER_NAME" that you mentioned in your previous note are listed in the previous code block you provided. It's going to be extremely difficult to help you solve your problem with the information that you've provided, when that information isn't the actual code that you are using. Does this makes sense?

So, if you want further help on this issue, please provide the actual code that you are having a problem with.

Thanks,

dpm1057 · September 23rd, 2011, 01:22 PM

On a whim I opened the record source on the form and added my combo boxes to the criteria for their corresponding fields. As soon as I did that, it worked.

gjgriffith · September 23rd, 2011, 03:38 PM

Hello dpm1057,

Thanks for the follow up note. I'm Extremely glad to hear that you got it working

. If there is anything else we can do to help please let us know!

And thank you again for reading the "Microsoft Access 2007 VBA Programmer's Reference", we truly appreciate your patronage!

Sincerely,