Hi David,
after stLinkCriteria = "[...
add this statement:
debug.print stLinkCriteria
then, after it runs, press Ctrl-G to Goto the debuG window. Copy what is there and paste into a message
is frmCustomerVehiclesSub a subform on the form that the code is behind? If so, use -->
me.frmCustomerVehiclesSub.Form!VehicleMakeID
your message did not have ID on the end of VehicleMakeID ... check the control names.
Also, what is the SQL for the RecordSource of the report that is being opened?
Are the controls with the criteria ON the form? (they can be not Visible)
~~~
it appears, from your formname (frmRepairOrder-Select4Report2), that you possibly have a form collecting criteria for EACH report? that is a lot of forms! I generally have one report menu for each module of a database. Reports can be chosen using a combo or listbox control (or command buttons for each). The criteria and its processing is shared.
~~~
Create a Report Filter (WhereCondition clause of OpenReport)
~~~
design a report to show the information (for ALL records)
set up a form to be used as a Report Menu with, for example:
comboboxes to choose specific criteria
a textbox or combo for Date1
a textbox or combo for Date2
a command button to launch the report
It is best to build a filter string for the report (as opposed to embedding a parameter in a query)--in this way, you can use textboxes, comboboxes, listboxes, etc to make it easier for the user to choose criteria and you can ignore criteria when it has not been specified...
~~~
here is an example that tests criteria and builds a filter string to use as a parameter in OpenReport for the WHERECONDITION clause
DoCmd.OpenReport reportname[, View][, FilterName][, WhereCondition]
ie:
DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"
~~~
********************************
********************************
I like to avoid dynamic criteria in queries ... and actually almost anywhere -- code writes a lot of SQL <smile>
This is used a lot:
CODE
DoCmd.OpenReport "ReportName", acPreview, , varCriteria
WHERE
varCriteria is how to limit the records. For instance -->
"CustomerID = " & Me.CustomerID
OR
"PartID = " & me.PartID & " AND OrderDate =#" & me.OrderDate & "#"
varCriteria 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
'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim varFilter as variant
'initialize the variable
varFilter = 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 varFilter hold the criteria
If not IsNull(me.text_controlname ) Then
varFilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if
'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
varFilter = (varFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if
'test the next control
If not IsNull(me.numeric_controlname ) Then
varFilter = (varFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if
if not IsNull(varFilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , varFilter
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
varFilter = (varFilter + " 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 would do:
CODE
If not IsNull(me.date1_controlname ) Then
varFilter = (varFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if
If not IsNull(me.date2_controlname ) Then
varFilter = (varFilter + " 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
Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF you want to also PRINT the criteria on the report
put a LABEL on your PageFooter
name --> Label_Criteria
then, in the OnFormat event of the ReportHeader
CODE
'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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.
Null + "anything" = Null
Null & "anything = "anything"
"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.
Trim((Firstname + " ") & (Middlename + " ") & Lastname)
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
Access Basics by Crystal (Bill Mosca's site)
http://thatlldoit.com
Free 100-page book that covers essentials in Access
Warm Regards,
Crystal
*
(: have an awesome day :)
*
--- On Sun, 9/4/11, David Tolson wrote:
> Thank you Crystal, I incorporated
> your code into my click event to open the
> form
>
>
>
> stLinkCriteria = "[CustomerID]=" &
> Me![CustomerID] & " AND
> [CustomerVehicleID] = " &
> [Forms]!frmCustomerContactData!frmCustomerVehiclesSub.Form!VehicleMake
>
>
>
> but no records show up and there should be 6. When open the
> form with only
> the first criteria
>
>
>
> stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
>
>
>
> all of the customer's vehicles show, but I want to only
> open vehicles that =
> a specific vehicle make
>
>
>
> I did try to open the form with the second criteria
>
>
>
> stLinkCriteria = "[CustomerVehicleID] = " &
> [Forms]!frmCustomerContactData!frmCustomerVehiclesSub.Form!VehicleMake
>
>
>
> but again, no records showed up. I placed a temporary field
> in the subform
> called 'VehicleMake' to see if the value that I expect
> shows up when the
> subform (the one calling the form that I want to meet the
> specific
> criteria), and it does. Not sure where to go from here.
>
>
>
> Thanks again for your assistance and any further help will
> be equally
> appreciated.
>
>
>
> r/David
>
>
>
> From: Crystal
>
>
>
> hi David,
>
> remove the delimiters around the 2nd part -- numbers are
> not delimited
>
> " AND [CustomerVehicleID] = " &
> [Forms]!frmCustomerContactData!frmCustomerVehiclesSub.Form!VehicleMakeID
>
> warm regards,
> Crystal
>
> * have an awesome day :)
>
> --- On Sat, 9/3/11, David Tolson wrote:
>
> >
> > "Data type mismatch in criteria expression"
> >
> >
> >
> > From: djsdaddy531
>
> >
> >
> >
> > Great Day, I have a form that I want to open a
> datasheet
> > form with multiple
> > conditions:
> >
> > Here is the code behind the click condition that opens
> the
> > form:
> >
> > stDocName = "frmRepairOrder-Select4Report2"
> > stLinkCriteria = "[CustomerID]=" &
> Me![CustomerID]
> > & " AND
> > [CustomerVehicleID] = '" &
> >
> [Forms]!frmCustomerContactData!frmCustomerVehiclesSub.Form!VehicleMakeID
> > &
> > "'"
> > DoCmd.OpenForm stDocName, , , stLinkCriteria
> >
> > The error message says
> >
> > Syntix error in string in query expression
> '[CustomerID]=1
> > AND
> > [CustomerVehicleID]= '2715'.
> >
> > I suspect that it has something to do with the fact
> the
> > Customer ID and
> > CustomerVehicleID are numbers and not strings, but I
> have
> > attempted
> > different variations of ' " marks, but to no avail.
> >
> > Thanks in advance for the help.
> >
> > r/David
> >
> >
>
>
Tidak ada komentar:
Posting Komentar