criteria

[Christina]

I have a couple of reports from queries that all prompt for criteria between
beginning date and ending date. I need to create a from to input the dates
and run all the reports.

 

[karl dewey]

Use an unbound form with unbound text boxes.
In the queries use this as criteria (use your actual names).
Between CVDate([Forms]![YourFormName]![TextBox1]) AND
CVDate([Forms]![YourFormName]![TextBox2])

You must open the form, enter dates, and let the form remain open while
running reports.

 

[Christina]

I am getting an error report ....Data Type mismatch in criteria expression.
THe name of my form is Paraform. If the input will be start date and end
date, would you can I rename the text boxes? I copied the criteria just as
in your reply. Please help.

THanks

Use an unbound form with unbound text boxes.
In the queries use this as criteria (use your actual names).
Between CVDate([Forms]![YourFormName]![TextBox1]) AND
CVDate([Forms]![YourFormName]![TextBox2])

You must open the form, enter dates, and let the form remain open while
running reports.

--
KARL DEWEY
Build a little - Test a little

I have a couple of reports from queries that all prompt for criteria between
beginning date and ending date. I need to create a from to input the dates
and run all the reports.

 

[karl dewey]

I am getting an error report ....Data Type mismatch in criteria expression.
My post uses the CVDate function to convert a text string to a datetime
value. Maybe your fields are not datetime datatype to produce this error.
Open the table in design view and check the datatype of the field.
Yes, rename to your text box names.

 

[Christina]

My date field is set to Med date. This is how my criteria looks now. Between
CVDate([Forms]![dialog]![Beginning Date]) And CVDate([Forms]![Dialog]![Ending
Date])


Thanks

 

[karl dewey]

What is the DataType of your date field? Is it DateTime or Text?
It should be DateTime.
--
KARL DEWEY
Build a little - Test a little

My date field is set to Med date. This is how my criteria looks now. Between
CVDate([Forms]![dialog]![Beginning Date]) And CVDate([Forms]![Dialog]![Ending
Date])


Thanks

My post uses the CVDate function to convert a text string to a datetime
value. Maybe your fields are not datetime datatype to produce this error.
Open the table in design view and check the datatype of the field.

Yes, rename to your text box names.

 

[Christina]

Yes it is DateTime

What is the DataType of your date field? Is it DateTime or Text?
It should be DateTime.
--
KARL DEWEY
Build a little - Test a little

My date field is set to Med date. This is how my criteria looks now. Between
CVDate([Forms]![dialog]![Beginning Date]) And CVDate([Forms]![Dialog]![Ending
Date])


Thanks

I am getting an error report ....Data Type mismatch in criteria expression.
My post uses the CVDate function to convert a text string to a datetime
value. Maybe your fields are not datetime datatype to produce this error.
Open the table in design view and check the datatype of the field.

can I rename the text boxes?
Yes, rename to your text box names.

 

[karl dewey]

Post the complete query SQL so it can be analyzed for the error of Data Type
mismatch in criteria expression.
--
KARL DEWEY
Build a little - Test a little

Yes it is DateTime

What is the DataType of your date field? Is it DateTime or Text?
It should be DateTime.
--
KARL DEWEY
Build a little - Test a little

My date field is set to Med date. This is how my criteria looks now. Between
CVDate([Forms]![dialog]![Beginning Date]) And CVDate([Forms]![Dialog]![Ending
Date])


Thanks

:

I am getting an error report ....Data Type mismatch in criteria expression.
My post uses the CVDate function to convert a text string to a datetime
value. Maybe your fields are not datetime datatype to produce this error.
Open the table in design view and check the datatype of the field.

can I rename the text boxes?
Yes, rename to your text box names.

 

[Christina]

SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE (((TblClients.Date) Between CVDate([Forms]![dialog]![Beginning Date])
And CVDate([Forms]![Dialog]![Ending Date])))
GROUP BY TblClients.Method;

Post the complete query SQL so it can be analyzed for the error of Data Type
mismatch in criteria expression.
--
KARL DEWEY
Build a little - Test a little

Yes it is DateTime

What is the DataType of your date field? Is it DateTime or Text?
It should be DateTime.
--
KARL DEWEY
Build a little - Test a little


:

My date field is set to Med date. This is how my criteria looks now. Between
CVDate([Forms]![dialog]![Beginning Date]) And CVDate([Forms]![Dialog]![Ending
Date])


Thanks

:

I am getting an error report ....Data Type mismatch in criteria expression.
My post uses the CVDate function to convert a text string to a datetime
value. Maybe your fields are not datetime datatype to produce this error.
Open the table in design view and check the datatype of the field.

can I rename the text boxes?
Yes, rename to your text box names.

 

[John W. Vinson]

SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE (((TblClients.Date) Between CVDate([Forms]![dialog]![Beginning Date])
And CVDate([Forms]![Dialog]![Ending Date])))
GROUP BY TblClients.Method;

Try this instead: explicitly define the parameters.

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE (((TblClients.[Date]) Between [Forms]![dialog]![Beginning Date]
And [Forms]![Dialog]![Ending Date]))
GROUP BY TblClients.Method;

I'd also suggest changing the name of the field Date to some other name; Date
is a reserved word for the builtin Date() function. In addition, if the Date
field contains both a date and a time you'll miss records that come on the
last day of the range unless you use

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE (((TblClients.[Date]) >= [Forms]![dialog]![Beginning Date]
And (TblClients.[Date]) < DateAdd("d", 1, [Forms]![Dialog]![Ending Date])))
GROUP BY TblClients.Method;

 

[Christina]

I am not getting any data when I run the query.
My query is made like this:
From TblClients:
Method Groupy By
Id Count (which is an autonumber and is the primary key)
Date Where

When go into SQL Specific there is Union, Pass through, Data Definition:
whcih one do I paste the code.
Also do I start with the word parameter.

Thanks

SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE (((TblClients.Date) Between CVDate([Forms]![dialog]![Beginning Date])
And CVDate([Forms]![Dialog]![Ending Date])))
GROUP BY TblClients.Method;

Try this instead: explicitly define the parameters.

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE (((TblClients.[Date]) Between [Forms]![dialog]![Beginning Date]
And [Forms]![Dialog]![Ending Date]))
GROUP BY TblClients.Method;

I'd also suggest changing the name of the field Date to some other name; Date
is a reserved word for the builtin Date() function. In addition, if the Date
field contains both a date and a time you'll miss records that come on the
last day of the range unless you use

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
WHERE (((TblClients.[Date]) >= [Forms]![dialog]![Beginning Date]
And (TblClients.[Date]) < DateAdd("d", 1, [Forms]![Dialog]![Ending Date])))
GROUP BY TblClients.Method;

 

[John W. Vinson]

I am not getting any data when I run the query.
My query is made like this:
From TblClients:
Method Groupy By
Id Count (which is an autonumber and is the primary key)
Date Where

When go into SQL Specific there is Union, Pass through, Data Definition:
whcih one do I paste the code.
Also do I start with the word parameter.

Sorry, I didn't mean the "SQL Specific" menu option - I meant SQL view
(View... SQL on the menu option, or selecting the SQL button from the dropdown
on the left end of the toolbar).

You can enter the parameters exactly as I posted them, starting with the word
PARAMETER, in SQL view; or you can right mouseclick the background of the
table window in query design view and select Paramteters from the menu. Copy
and paste the parameters into the left column (they must match *exactly*) and
choose date/time from the dropdown in the right column.

 

[Christina]

Sorry, I still get no data. Any further help would be greatly apreciated.

 

[John W. Vinson]

Sorry, I still get no data. Any further help would be greatly apreciated.

Please open your Query in design view. Select View... SQL from the menu and
copy and paste the entire SQL text to a message here. Also copy and paste a
few rows of the actual data from your table (obfuscate any confidential data
of course, just leave the date field), and indicate EXACTLY what values you
have typed into the form textboxes.

 

[Christina]

I got it to work. I was doing something wrong. My next question would be
what part of that code would I use on my report so that it shows up the date
period on my report.

THANKS so much.

 

[Christina]

Would you please help me with his one.
I have a query:
Query from table CheckBook:

Field CheckNumber Name , DateTime , Date, CheckAmount ,
DepositAmount , Details , Status (where the criteria is No


I have two Date fields in the table. One showing the time of the
transaction called DateTime, another called Date with just date.

I would like it grouped by Date, and have the DateTime showing up in the
query.

 

[John W. Vinson]

I got it to work. I was doing something wrong. My next question would be
what part of that code would I use on my report so that it shows up the date
period on my report.

Just put textboxes on the report with Control Sources like

=[Forms]![dialog]![Beginning Date]

 

[Christina]

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount AS [Check Amount], CheckBook.DepositAmount AS [Deposit
Amount], CheckBook.Details, CheckBook.Status, Sum(CheckBook.CheckAmount) AS
CountOfID, CheckBook.Date
FROM CheckBook
GROUP BY CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount, CheckBook.DepositAmount, CheckBook.Details,
CheckBook.Status, CheckBook.Date
HAVING (((CheckBook.Status)="no"));

 

[Christina]

This is the code, but the results is not for the period I specify.

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT TblClients.Method, Count(TblClients.ID) AS CountOfID
FROM TblClients
GROUP BY TblClients.Method;


Please help. My deadline is tomorrow.

THANKS

 

[John W. Vinson]

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount AS [Check Amount], CheckBook.DepositAmount AS [Deposit
Amount], CheckBook.Details, CheckBook.Status, Sum(CheckBook.CheckAmount) AS
CountOfID, CheckBook.Date
FROM CheckBook
GROUP BY CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount, CheckBook.DepositAmount, CheckBook.Details,
CheckBook.Status, CheckBook.Date
HAVING (((CheckBook.Status)="no"));

On the other one, I tried with a code adapted from the one you made, but it
returns all status NO, regardless of the date.

Exactly. That's what you're asking for!!!!

You have Parameters defined... *but you're not using them*. Defining a
parameter just tells Access "I'm going to be asking for something and I want
it to be considered a date/time field when I do"; it doesn't change the query
unless you use it.

You say elsewhere in the thread:

I have two Date fields in the table. One showing the time of the
transaction called DateTime, another called Date with just date.


That's a MAJOR MISTAKE and completely unnecessary. What if your DateTime field
contained #3/5/2009 11:30:00#, and your Date field contained #12/25/2005#? One
of them's wrong; can you tell (based on the content of the record) which one
is wrong? You only need *one* field if you're only storing one point of time.
You can display the field twice, if you wish to see the date only in one case
and the time only (or the date and time) in another.

That said... try changing your query to

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount AS [Check Amount], CheckBook.DepositAmount AS [Deposit
Amount], CheckBook.Details, CheckBook.Status, Sum(CheckBook.CheckAmount) AS
CountOfID, CheckBook.Date
FROM CheckBook
GROUP BY CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount, CheckBook.DepositAmount, CheckBook.Details,
CheckBook.Status, CheckBook.Date
WHERE (((CheckBook.Status)="no"))
AND CheckBook.[Date] >= [Forms]![dialog]![Beginning Date]
AND ChekcBook.[Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date]);

This query will probably not give you what you want though - since it's a
Totals query grouping by check number, name, date, etc. etc.; you'll see all
of these fields exactly once with its amount as the SumOfAmount and 1 as the
CountOfID. Since I don't clearly understand what you DO want it's hard to
suggest how to change the query. Assuming that you just want the sum of
amounts and the count of transactions for all checks in that date range, just
remove the fields you don't want to see:

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT Count(*) AS CountOfID,
Sum(CheckBook.CheckAmount) AS SumOfAmount
FROM CheckBook
WHERE CheckBook.Status="no"
AND CheckBook.[Date] >= [Forms]![dialog]![Beginning Date]
AND CheckBook.[Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date]);

If you just want to see all records with all fields within that date range,
without totals or counts, just don't use the Group By:

PARAMETERS [Forms]![dialog]![Beginning Date] DateTime,
[Forms]![dialog]![Ending Date] DateTime;
SELECT CheckBook.CheckNumber, CheckBook.Name, CheckBook.DateTime,
CheckBook.CheckAmount AS [Check Amount], CheckBook.DepositAmount AS [Deposit
Amount], CheckBook.Details, CheckBook.Status, CheckBook.Date
FROM CheckBook
WHERE CheckBook.Status="no"
AND CheckBook.[Date] >= [Forms]![dialog]![Beginning Date]
AND CheckBook.[Date] < DateAdd("d", 1, [Forms]![dialog]![Ending Date]);