I am not exactly sure what's going on, but there is an issue I am running into. My date entries don't show when I run my SQL statement on a search form.
here is a post I came across that talks about the same issue I am going through
Code:
Function SearchCriteria()
Dim strProjectCode, strRequestNumber, strCompName, strSdate, strRdate As String
Dim task, strCriteria As String
'Check if Project Code Textbox is blank
If IsNull(Me.txtS_PC) Then strProjectCode = "[ProjectCode] like '*'" Else
strProjectCode = "[ProjectCode] = '" & Me.txtS_PC & "'"
End If
'Check if Request Number Textbox is blank
If IsNull(Me.txtS_RN) Then strRequestNumber = "[RequestNumber] like '*'" Else
strRequestNumber = "[RequestNumber] = '" & Me.txtS_RN & "'"
End If
'Check if Company Name Textbox is blank
If IsNull(Me.txtS_CompNa) Then strCompName = "[companyName] like '*'" Else
strCompName = "[companyName] = '" & Me.txtS_CompNa & "'"
End If
'Check if Sent Date Textbox is blank
If IsNull(Me.txt_Search_Sdate) Then strSdate = "[DateRequestSent] like '*'" Else
strSdate = "[DateRequestSent] = #" & txt_Search_Sdate & "#"
End If
'Check if Received Date Textbox is blank
If IsNull(Me.txt_Search_Rdate) Then strRdate = "[DateReceived] like '*'" Else
strRdate = "[DateReceived] = #" & txt_Search_Rdate & "#"
End If
strCriteria = strProjectCode & "And" & strRequestNumber & "And" & strCompName & "And" & strSdate & "And" & strRdate
task = "SELECT * from qryRequestInternal where " & strCriteria
Me.sfrmRequestInternal.Form.RecordSource = task
Me.sfrmRequestInternal.Form.Requery
Me.sfrmRequestInternal_col.Form.RecordSource = task
Me.sfrmRequestInternal_col.Form.Requery
End Function
Probably an issue with date string formats - they need to be in the US format of 'mm/dd/yyyy' or the sql generic of 'yyyy-mm-dd'. If this is the case use the format function
format(txt_Search_Rdate,"yyyy-mm-dd")
For the future, when posting code use the </> button, not the >_ button, will make it easier for others to read
All, I have a query 'qry_Max_Price_Part_Supplier' that returns the latest cost of a product that could be supplied by various suppliers(works). The query contains the following fields 'MaxOfDate_of_Price', ' Cost_Price', "ProductID' (number) & 'JimCode' (string). I wish to return the latest...
one problem I see is that "And" should be " And ", with spaces around it
the Dim statements should explicitely define data type for each variable. For instance, in this statement.
Dim task, strCriteria As String
only strCriteria will be a string. Since task wasn't declared, it will be a variant. This is the right way: Dim task As String, strCriteria As String
All the suggestions are valid so must be something else. provide the code you are now using, some example data, the values you have in your two searchboxes and the result required from that example data
Changing regional settings is a bit extreme, better to use the format function
All the suggestions are valid so must be something else. provide the code you are now using, some example data, the values you have in your two searchboxes and the result required from that example data
Changing regional settings is a bit extreme, better to use the format function
Here is the issue. Actually, I should clarify that old existing data can be filtered using the search form with the provided function. The problem is when I enter new data. It is not being captured by the filter. When I played with the date settings earlier (before posing my question) it worked briefly then stopped working later. Could it be the data entry form?
Edit: Okay I just realized nothing is being filtered when I enter new data in the tables, not just the dates. What could it possibly be?
Thank you for your input. I believe my problem now lies here. Apparently, when a field is empty or null it's not showing up in the results. I am going to try your code and update you.
sure ... look at the code you have to test Me.txt_Search_Rdate and make the string to incoporate. I used WITH and END WITH because the control was referenced more thanone time -- this is better performance. Then, when you want the value in that control, .Value is specified.
HOWEVER, to make it easier on yourself ... I suggest concatenating " AND " only if something is there to add, which won't happen if string to possibly add is defined as string instead of variant. Strings can't be Null (no value) whereas Variants can.
I'm not going to change all the code for you ... instead I tell you how to understand so that you can do it yourself (smile)
instead of this:
strCriteria = strProjectCode & "And" & strRequestNumber & "And" & strCompName & "And" & strSdate & "And" & strRdate 'wrong anyway -- needs space dlimiters and more
you should just start with vCriteria = Null -- and only add onto it if there is something to consider. In the following example,
vCriteria is how to limit the records. For instance -->
"CustomerID = " & Me.CustomerID
OR
"PartID = " & me.PartID & " AND OrderDate =#" & me.OrderDate & "#"
vCriteria is the WHERE clause of an SQL statement without the word "where"
another advantage of using controls on forms is that you can ignore the criteria is the user didn't fill anything out because you can test for that in your code.
~~~
assuming you are in the code behind the ReportMenu form... here is a general case:
CODE
'~~~~~~~~~~~~~~~~~~~~~~~~~~
Rich (BB code):
'tell Access you are going to create a variabledim vCriteria as variant'initialize the variable
vCriteria = null'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make vCriteria hold the criteriaIf not IsNull(me.text_controlname ) Then
vCriteria = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if'test the next controlIf not IsNull(me.date_controlname ) Then'if we alread have criteria, add AND to specify that and more
vCriteria = (vCriteria + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if'test the next controlIf not IsNull(me.numeric_controlname ) Then
vCriteria = (vCriteria + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end ifif not IsNull(vCriteria) then
DoCmd.OpenReport "ReportName",acViewPreview,,vCriteria
else
DoCmd.OpenReport "ReportName",acViewPreview
endif
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'SPACE UNDERSCORE at the end of a line means that the statement is continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have used:
TextFieldname to show how text needs to be delimited - with single quote marks (you can also use double quote marks
DateFieldname to show that dates need to be delimited with #
NumericFieldname to show that numbers are not delimited
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter string already says something)
Then we are going to add the criteria for that filter
vCriteria = (vCriteria + " AND ") & ... ' some other criteria
make sure that the referenced fields are in the underlying RecordSet for the report -- and it is sometimes necessary for the fields to be in controls on the report (the Visible property can be No)
For a Date Range, you might do:
Rich (BB code):
If not IsNull(me.date1_controlname ) Then
vCriteria = (vCriteria + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end ifIf not IsNull(me.date2_controlname ) Then
vCriteria = (vCriteria + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use the ISO 8601 representation of the date:
CODE
Rich (BB code):
Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"
NOTE: consider time if it might be there
emphasizing what @CJ_London said, "Changing regional settings is a bit extreme, better to use the format function"
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
& and + are both Operators
The standard Concatenation Operator is ampersand (&). If a term that is concatenated is Null (has no data; unknown), all terms will display if you use ampersand.
The Addition Operator is the plus sign (+) … but, even if one of the terms has a value, the result will be Null if any term is Null (kind of like multiplying by 0). As in math, what is enclosed in parentheses will be evaluated first.
"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value
Null + "" = Null
Null & "" = ""
(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term is concatenated -- and note the space in the result before Lastname
Do you see the difference between using + and using & ? For instance, if you want to add a space between first and last name but you are not sure that first name will be filled out, you can do this:
(Firstname + " ") & Lastname
What is in the parentheses is evaluated first -- then it is concatenated to what comes next
You might also want to do this:
(Firstname + " ") & (Middlename + " ") & Lastname
Combining + and & in an expression gives you a way to make the result look right without having to test if something is not filled out.
What if firstname is filled but nothing else? There will be a space at the end. Usually, this is not a problem but if you want to chop it off, you can wrap the whole expression in the Trim function, which truncates leading and trailing spaces.
for more on delimiters and other fundamental topics, read Access Basics*. All your fields come from tblJob. Your data structure needs to be normalized. Especially focus on the chapters on Normalization and Relationships in Access Basics
Let me jump on the DO NOT change your computer settings to solve the SQL date format train. That won't help anyone else who runs the application. Do it right. Do it with the Format() function and it will work for everyone Not just the people with US date settings.
And for debugging, ALWAYS print out the completed SQL string and post that. That makes it possible for us to actually see what the string is and therefore what the problems might be. You are on the right track and building the string. Now you just need to use it for the reason you are building it - it is a debugging tool. Otherwise, there is absolutely no reason to build the string into a variable.
More about the date issue - dates are stored internally as double precision numbers. The integer part of the field is the number of days since Dec 30, 1899 in the case of Access. SQL Server and Excel use the same technique but with a different origin date so their date ranges are slightly different from the date range supported by Access. The decimal part is the elapsed time since midnight so 1.25 = Dec 31, 1899 at 6 AM. Negative numbers are dates prior to Dec 30, 1899. So, when two date fields are compared, it is NOT a string compare it is a numeric compare. Now we get to the problem. When you build the SQL in VBA, you create a string with a date in it. That is why you need to enclose the date in #'s and it is why you need the date to be either in US format - mm/dd/yyyy OR the unambiguous yyyy-mm-dd. You could use short month as in dd-mmm-yyyy but that causes language issues so, if your default date format is NOT US standard then you need to convert the string date to one of the two acceptable formats.
To increase your understanding, if you use a querydef and refer to a date field on a form, you don't have the problem.
Select ... From ... Where SomeDate = Forms!yourform!yourdatecontrol
In this case, as long as the form control is bound to a datetime data type or defined as a date by having its format property set to some date format, then Access will treat it as a date because it is stored in the control as a double precision number and no conversion is necessary. Access isn't looking at the string you are seeing, it is looking at the actual numeric value.
However if you create the same query as above but do it in VBA instead of as a queyrdef,
strSQL = "Select ... From ... Where SomeDate = " & Forms!yourform!yourdatecontrol
you have the date problem. It ONLY occurs when a date is a string rather than a datetime data type. Understanding that tiny fact will help you to avoid the issue forever
@strive4peace
Thank you so much, Crystal! I believe that the search function is working properly. Thank you!
Code:
Function SearchCriteria()
Dim vCriteria As Variant
vCriteria = Null
'Check if Project Code Textbox is blank
If Not IsNull(Me.ProjectCode) Then
vCriteria = "[code] = '" & Me.ProjectCode & "'"
End If
'Check if Request Number Textbox is blank
If Not IsNull(Me.RequestNumber) Then
'if we alread have criteria, add AND to specify that and more
vCriteria = (vCriteria + " AND ") & "[requestNo] = '" & Me.RequestNumber & "'"
End If
'Check if Company Name Textbox is blank
If Not IsNull(Me.companyName) Then
'if we alread have criteria, add AND to specify that and more
vCriteria = (vCriteria + " AND ") & "[company] = '" & Me.companyName & "'"
End If
'Check if Sent Date Textbox is blank
If Not IsNull(Me.date) Then
vCriteria = (vCriteria + " AND ") & "[date] = #" & Me.date & "#"
End If
If Not IsNull(vCriteria) Then
task = "SELECT * from qryForm where " & vCriteria
Me.sfrmForm.Form.RecordSource = task
Me.sfrmForm.Form.Requery
End If
End Function
All the suggestions are valid so must be something else. provide the code you are now using, some example data, the values you have in your two searchboxes and the result required from that example data
Thank you all so much for the input. Apparently, my search function was skipping records with empty fields and hence why I was not getting the records that I was adding new to the databases (I was adding records and skipping the fields to test whether the date format worked) and I ended up solving the date issue without knowing and inadvertently ran into a different issue due to a poor search function. Thank you, Crystal. I believe the search function is now working properly as intended.
