Search on dates

[BobNTN]

Geez, I hate to have to ask this but...

Using a search form with beginning date and ending dates as criteria.
Trying to populate 8 text boxes from the results.
In the query, I am using a Between & And statement on the date. In the other 8 fields, using a Like statement.
The query works without the Like statements but returns results on a datasheet screen - doesn't pop the fields in the form.
The query prompts for parameter on the Like statements fields if entered which of course, doesn't return anything.

Here is my query:

SELECT Sum(TblInvoice.miles) AS SumOfmiles, Sum(TblInvoice.partscost) AS SumOfpartscost, Sum(TblInvoice.lodging) AS SumOflodging, Sum(TblInvoice.perdiem) AS SumOfperdiem, Sum(TblInvoice.workhours) AS SumOfworkhours, ([Sumofperdiem]*45) AS Totperdiem, ([Sumofworkhours]*100) AS Totwrkhrs, ([Totperdiem]+[Totwrkhrs]+[Sumofmiles]+[Sumofpartscost]+[Sumoflodging]) AS Total
FROM TblInvoice
HAVING (((Sum(TblInvoice.miles)) Like [Forms]![FrmDates].[Qmiles] & "*") AND ((Sum(TblInvoice.partscost)) Like [Forms]![FrmDates].[Qparts] & "*") AND ((Sum(TblInvoice.lodging)) Like [Forms]![FrmDates].[Qlodge] & "*") AND ((Sum(TblInvoice.perdiem)) Like [Forms]![FrmDates].[Qperdiem] & "*") AND ((Sum(TblInvoice.workhours)) Like [Forms]![FrmDates].[Qhours] & "*") AND ((([Sumofperdiem]*45)) Like [Forms]![FrmDates].[Qperdiemcost] & "*") AND ((([Sumofworkhours]*100)) Like [Forms]![FrmDates].[Qhourcost] & "*") AND ((([Totperdiem]+[Totwrkhrs]+[Sumofmiles]+[Sumofpartscost]+[Sumoflodging])) Like [Forms]![FrmDates].[Qtotal] & "*") AND ((TblInvoice.date) Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]));

Is the Like statements my problem ?
Head is exploding - need help!

 

[HiTechCoach]

It appears that you are trying to use a "Like" for numeric data? "Like" is used for alphanumeric data (text). Where you want to match part of a string.

Numbers match ( 1 =1 ) or don't match ( 1 <> 2 ). You probably will need tot change the Like to =

 

[namliam]

TblInvoice.date

It is advicable to not use reserved words (date) as a column name...

 

[BobNTN]

TblInvoice.date

It is advicable to not use reserved words (date) as a column name...

Thanks - I thought of that last night and changed it to edate

 

[BobNTN]

It appears that you are trying to use a "Like" for numeric data? "Like" is used for alphanumeric data (text). Where you want to match part of a string.

Numbers match ( 1 =1 ) or don't match ( 1 <> 2 ). You probably will need tot change the Like to =

I have tried = but each field still prompts

What I am trying to do is populate a form / report based on beginning and ending dates. It has to be something other than 'Like' but can't seem to come up with it. As I said, I can take the whole Like statements out and when I run it from the form, it displays the results in datasheet view rather than populating the form.

 

[BobNTN]

Am I going about what I'm trying to do all wrong ?

 

[HiTechCoach]

Am I going about what I'm trying to do all wrong ?

YOu shoudl be able to do what you want. I use form reference a lot to get criteria in reports.

Are you keeping the form [FrmDates] opened when you try to open the report?


This example may help:
Report Dialog Examples

 

[BobNTN]

YOu shoudl be able to do what you want. I use form reference a lot to get criteria in reports.

Are you keeping the form [FrmDates] opened when you try to open the report?


This example may help:
Report Dialog Examples

Yes, I have a form with beginning and ending date text boxes, unbound, then I have the 8 fields I am trying to populate from the query based on the dates input.
I tried doing this with the date ranges in an unbound main form and the 8 populated fields in a sub form last night.
When I open the form, it gives me one of those messages about the expression being too complicated. Close that then put the dates in and it blows away the subform.

Your sample is great but a little too complicated for me to convert.

I started this to print an invoice based on a time frame due to the way billing will have to be done. And I need the beginning and end dates to be printed on the invoice. Can't come up with any alternative, yet.
I think the 'Like' word is at least one of the problems. It works without them but sends it to datasheet view, not the form fields.

 

[HiTechCoach]

... then I have the 8 fields I am trying to populate from the query based on the dates input

Are you using bound control on the form to pull the data from the query?

Your query is using controls on the form [FrmDates] as criteria in your query to filter the query.

So you have a form named: FrmDates

You enter data data into these controls:

Qmiles
Qparts
Qlodge
Qperdiem
Qhours
Qperdiemcost
Qhourcost
Qtotal

begdate
enddate

and then all 10 controls on the form are used to filter your query:

Like this:

...
HAVING (((Sum(TblInvoice.miles)) Like [Forms]![FrmDates].[Qmiles] & "*") AND ((Sum(TblInvoice.partscost)) Like [Forms]![FrmDates].[Qparts] & "*") AND ((Sum(TblInvoice.lodging)) Like [Forms]![FrmDates].[Qlodge] & "*") AND ((Sum(TblInvoice.perdiem)) Like [Forms]![FrmDates].[Qperdiem] & "*") AND ((Sum(TblInvoice.workhours)) Like [Forms]![FrmDates].[Qhours] & "*") AND ((([Sumofperdiem]*45)) Like [Forms]![FrmDates].[Qperdiemcost] & "*") AND ((([Sumofworkhours]*100)) Like [Forms]![FrmDates].[Qhourcost] & "*") AND ((([Totperdiem]+[Totwrkhrs]+[Sumofmiles]+[Sumofpartscost]+[Sumoflodging])) Like [Forms]![FrmDates].[Qtotal] & "*") AND ((TblInvoice.date) Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]));

You query is only pulling data from the 10 control on the form to filter the data in the query.

Are you trying to use the query to put data in the eight controls that are not the date range? This is not what your query can be used to do. It does the opposite. It is pulling data form the controls!


Maybe try just:

...
HAVING  ((TblInvoice.date) Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]);

 

[BobNTN]

You query is only pulling data fromt he 10 control on the form to filter the data in the query.

Are you trying to use the query to put data in the eight controls that are not the date range? This is not what your query can be used to do. It does the opposite. It is pulling data form the controls!

I ONLY want to search on the date field 'edate' (renamed it)
then it populate the other 8 fields in the form based on the date range criteria
I do not want it to prompt any of the other 8.
I will try to do a screenshot of the form.

ok, how do you paste an image in here ?

 

[BobNTN]

try this one

 

[BobNTN]

HAVING ((TblInvoice.date) Between [Forms]![FrmDates].[begdate] And [Forms]![FrmDates].[enddate]);
tried this, WITHOUT any of the 'Like' statements in the other fields. works but pops up in data sheet view.

 

[HiTechCoach]

What code do you have in the run command button? My guess it is the code there that is the issue.

To use unbound controls, you will need to use VBA code to open the query as a recordset. You will have to use VBA code to place the eight values form the query into each of the each unbound controls.

Is there some reason you are not using a bound form? It would probably be a lot easier to implement that an unbound form. Unbound forms generally requires a lot more effort (like VBA coding).

 

[BobNTN]

for the run button

docmd.openquery "QryInvTotes", AcViewNormal

End Sub

I tried addin Me.Refresh after the docmd line but made no difference
I used the unbound so I could make the form look like a report to print plus I want the begin and end dates to appear for print also

Mostly, I had no clue how to do a report based on a date range.
seems I don't know how to do a form either

 

[HiTechCoach]

for the run button

docmd.openquery "QryInvTotes", AcViewNormal

End Sub

I tried addin Me.Refresh after the docmd line but made no difference
I used the unbound so I could make the form look like a report to print plus I want the begin and end dates to appear for print also

Mostly, I had no clue how to do a report based on a date range.
seems I don't know how to do a form either

The code:

docmd.openquery "QryInvTotes", AcViewNormal

opens a query in the datasheet mode, jsut like you are seeing. That is waht it should do.

There good news is that Access is working correctly based on the command that you have giving it. Unfortunately, that is not really what you want.

This would be so easy to do as a report. Create a report that is bases ion the query. Add bound controls onto the report.

change the docmd.openquery to Docmd.OPenReport ...

Now you can print the data.


To get the data range on your report, place a text box on the report and set the control source to be:

= "From: " & [Forms]![FrmDates].[begdate] & " to " &  [Forms]![FrmDates].[enddate]

If you must use a form, I would make a form that is bound to the query just like you would do a report. Then open the form instead of the report.

 

[BobNTN]

Thanks coach.
Makes sense - I will try tomorrow. bad sinus prob today

so I don't put any criteria in the query at all using what you gave ?

 

[HiTechCoach]

The only criteria (form references) will be the filter for the date range.

 

[BobNTN]

Coach,
I tried it as a report with text box as you said, it works except does not display the dates on the report. Just shows #Name
puzzled also, why use, or why it works using [Forms]![FrmDates] when I'm using a report (RptInvoice) instead ?

I can live with using a report. I'll just have to do some work on it.

 

[HiTechCoach]

If you are getting #Name, then you probably have a syntax issue ort the name of the Test box in confusing Access. I use this technique on almost every report.

YOu do not have to do it with just a report. As stated previously you coudl open a form just as easily as the report to do the same thing.

If you want have the data on the same for as the "run" command button, then I would use a sub form. I would keep the sub form not visible until you click your run button. This would make the sub form visible and requery the sub form.

 

[BobNTN]

well since the text box has both dates, didn't know what to name it and the criteria in the query refers to both begdate and enddate.