Multiple Date Parameters In Query

[hobbit2612 via AccessMonster.com]

Hi, I wonder whether someone may be able to help me please.

Thanks to John Spencer from this forum I have managed to track changes I make
in my database tables.

I now want to take the updated fields and put them in a report which, I'm
fairly comfortable with. However, the problem I'm having is with the query.

There are a good number of 'DateFieldChanged' fields in my query and I would
like to be able to use a date parameter to extract the data I want in my
report but with only having to type the dates in once. Please note that some
of the fields will have a 'DateofChange' entry, others won't.

I thought the best way to do this was to create a pop up form with
'StartDate' and 'EndDate' text boxes and a button to run the query.

In the query I have included the statement:

Select * From MyTable Where [StartDate] >= Forms![MyForm]![StartDate] And
[EndDate] <= Forms![MyForm]![EndDate]

The problem is that when I type the dates in on the form and click the button
I am asked to type the dates in again within a dialog box that says 'Enter
Parameter Value'. I'm assuming this is coming from the query. I type in the
dates and click ok. But the query extracts all the data even though it
doesn't fall within the date parameters.

If I go directly into the query and try it the same thing happens.

Could someone please point me in the right direction.

Many thanks

Chris

 

[John W. Vinson]

Hi, I wonder whether someone may be able to help me please.

Thanks to John Spencer from this forum I have managed to track changes I make
in my database tables.

I now want to take the updated fields and put them in a report which, I'm
fairly comfortable with. However, the problem I'm having is with the query.

There are a good number of 'DateFieldChanged' fields in my query and I would
like to be able to use a date parameter to extract the data I want in my
report but with only having to type the dates in once. Please note that some
of the fields will have a 'DateofChange' entry, others won't.

I thought the best way to do this was to create a pop up form with
'StartDate' and 'EndDate' text boxes and a button to run the query.

In the query I have included the statement:

Select * From MyTable Where [StartDate] >= Forms![MyForm]![StartDate] And
[EndDate] <= Forms![MyForm]![EndDate]

The problem is that when I type the dates in on the form and click the button
I am asked to type the dates in again within a dialog box that says 'Enter
Parameter Value'. I'm assuming this is coming from the query. I type in the
dates and click ok. But the query extracts all the data even though it
doesn't fall within the date parameters.

If I go directly into the query and try it the same thing happens.

This could just be a name - or even spelling - error. Is the Name property of
the form you're using in fact "MyForm"? Are the textboxes in fact named
"StartDate" and "EndDate" (and not, say "Start Date" or "End_Date")? Is the
form in fact open when you try to open the report? Could you post the button's
Click event code?

If the DateFieldChanged value is NULL, I presume you don't want to see the
record; you won't with that criterion.

 

[hobbit2612 via AccessMonster.com]

Hi John, many thanks for taking the time to reply.

I've checked the spelling of the form and associated fields on the query and
they match. Please find below the code I am using in the query with the names
of my table, form and fields

FROM tblCR
WHERE ((([StartDate])>=[Forms]![frmDateInput]![StartDate]) AND (([EndDate])<=
[Forms]![frmDateInput]![EndDate]));

I have also posted the button click event below. I know that strictly
speaking the 'Command6' should be changed to a more conventional name, but
I've just been using this to test the form and will chaage when I get it to
work.

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim stDocName As String

stDocName = "qryCRUpdatedRecords"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

In terms of whether I would like to see the record if the fields are blank,
then as long as there is one field that has been updated I would like to see
the the whole record.

I've just been looking at the query again, and I'm wondering whether the
query fisn't working for one or both or none of these reasons.

The SQL shows all the records from the table which includes tick boxes, I'm
not sure whether this makes any difference and linked with this the query not
only outputs the fields that shows the updated date, it shows all the other
fields in the table.

I hope this helps, and once again many thanks for your help.

Regards

Chris

Hi, I wonder whether someone may be able to help me please.

[quoted text clipped - 24 lines]

If I go directly into the query and try it the same thing happens.

This could just be a name - or even spelling - error. Is the Name property of
the form you're using in fact "MyForm"? Are the textboxes in fact named
"StartDate" and "EndDate" (and not, say "Start Date" or "End_Date")? Is the
form in fact open when you try to open the report? Could you post the button's
Click event code?

If the DateFieldChanged value is NULL, I presume you don't want to see the
record; you won't with that criterion.

 

[John W. Vinson]

Hi John, many thanks for taking the time to reply.

I've checked the spelling of the form and associated fields on the query and
they match. Please find below the code I am using in the query with the names
of my table, form and fields

FROM tblCR
WHERE ((([StartDate])>=[Forms]![frmDateInput]![StartDate]) AND (([EndDate])<=
[Forms]![frmDateInput]![EndDate]));

Is the field *IN THE TABLE* tblCR in fact named [StartDate]? and do you have a
field named [EndDate]?

I have also posted the button click event below. I know that strictly
speaking the 'Command6' should be changed to a more conventional name, but
I've just been using this to test the form and will chaage when I get it to
work.

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim stDocName As String

stDocName = "qryCRUpdatedRecords"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

I would suggest that the button should open the Report, and that the Report be
based on the query - there's little benefit (other than debugging) to opening
a query datasheet.

In terms of whether I would like to see the record if the fields are blank,
then as long as there is one field that has been updated I would like to see
the the whole record.

I've just been looking at the query again, and I'm wondering whether the
query fisn't working for one or both or none of these reasons.

The SQL shows all the records from the table which includes tick boxes, I'm
not sure whether this makes any difference and linked with this the query not
only outputs the fields that shows the updated date, it shows all the other
fields in the table.

Please post the *complete* SQL of the query. It makes no difference which
fields it outputs (those would be in the SELECT clause); the criteria apply
only to the fields which are in the WHERE clause.

I hope this helps, and once again many thanks for your help.

My guess is that you do in fact not have fields in the table named StartDate
and EndDate. Do you have *multiple* date/time fields in the table? What are
their names?

 

[hobbit2612 via AccessMonster.com]

John,

Please find below the SQL for the query, but I think you've already hit the
nail on the head. The StartDate and EndDate are not fields in my table.

SELECT *
FROM tblCR
WHERE ((([StartDate])>=[Forms]![frmDateInput]![StartDate]) AND (([EndDate])<=
[Forms]![frmDateInput]![EndDate]));

The table contains a number of Date fields but none with a time stamp. What
I've done is say for example I have a field called 'DateOf Invoice' I have
another field in the same table which I use to collect the date of change
called' DateOf InvoiceChanged'. This bit is working a treat, but I am slowly
learning about SQL as you can probably tell and I'm just wasn't sure about
what to do about using the date paramater without having to type it in
multiple times.

Many thanks once again for your time and trouble.

Regards

Hi John, many thanks for taking the time to reply.

[quoted text clipped - 5 lines]

WHERE ((([StartDate])>=[Forms]![frmDateInput]![StartDate]) AND (([EndDate])<=
[Forms]![frmDateInput]![EndDate]));

Is the field *IN THE TABLE* tblCR in fact named [StartDate]? and do you have a
field named [EndDate]?

I have also posted the button click event below. I know that strictly
speaking the 'Command6' should be changed to a more conventional name, but

[quoted text clipped - 17 lines]

End Sub

I would suggest that the button should open the Report, and that the Report be
based on the query - there's little benefit (other than debugging) to opening
a query datasheet.

In terms of whether I would like to see the record if the fields are blank,
then as long as there is one field that has been updated I would like to see

[quoted text clipped - 7 lines]

only outputs the fields that shows the updated date, it shows all the other
fields in the table.

Please post the *complete* SQL of the query. It makes no difference which
fields it outputs (those would be in the SELECT clause); the criteria apply
only to the fields which are in the WHERE clause.

I hope this helps, and once again many thanks for your help.

My guess is that you do in fact not have fields in the table named StartDate
and EndDate. Do you have *multiple* date/time fields in the table? What are
their names?

 

[John W. Vinson]

The table contains a number of Date fields but none with a time stamp. What
I've done is say for example I have a field called 'DateOf Invoice' I have
another field in the same table which I use to collect the date of change
called' DateOf InvoiceChanged'. This bit is working a treat, but I am slowly
learning about SQL as you can probably tell and I'm just wasn't sure about
what to do about using the date paramater without having to type it in
multiple times.

In that case put the criterion under the field *that you want to search* in
the query grid. If you want it to search several fields, put the same
criterion under each of the fields, on separate lines in the query grid so it
will use OR logic. The equivalent SQL would resemble

SELECT *
FROM tblCR
WHERE
([DateOf Invoice]>=[Forms]![frmDateInput]![StartDate]
AND [DateOf Invoice])<= [Forms]![frmDateInput]![EndDate])
OR
([DateOf InvoiceChanged]>=[Forms]![frmDateInput]![StartDate]
AND [DateOf InvoiceChanged])<= [Forms]![frmDateInput]![EndDate])
;

The criterion in the WHERE clause is simply a true or false expression: is the
field in the table ([DateOf Invoice] say) in fact greater or equal to the date
value in the textbox StartDate and less than or equal to the textbox EndDate?
If that statement is TRUE, return the record; if it's FALSE, don't return the
record. You need to understand that the words OR and AND are operators in
Boolean algebra, just like + and - are operators in arithmatic; X AND Y is a
TRUE statement if both X and Y are TRUE, it's false otherwise; X OR Y is a
TRUE statement if X is true, or if Y is true or if both are true, it's FALSE
only if both X and Y are false.

 

[hobbit2612 via AccessMonster.com]

John,

Many thanks for all your time and trouble, it really is appreciated.

I've had a look at the coding you posted and with some minor tweaking to
include all my fields it works a treat.

I can't tell you what a relief it is to get this working.

Once again sincere thanks

Regards

Chris

The table contains a number of Date fields but none with a time stamp. What
I've done is say for example I have a field called 'DateOf Invoice' I have

[quoted text clipped - 3 lines]

what to do about using the date paramater without having to type it in
multiple times.

In that case put the criterion under the field *that you want to search* in
the query grid. If you want it to search several fields, put the same
criterion under each of the fields, on separate lines in the query grid so it
will use OR logic. The equivalent SQL would resemble

SELECT *
FROM tblCR
WHERE
([DateOf Invoice]>=[Forms]![frmDateInput]![StartDate]
AND [DateOf Invoice])<= [Forms]![frmDateInput]![EndDate])
OR
([DateOf InvoiceChanged]>=[Forms]![frmDateInput]![StartDate]
AND [DateOf InvoiceChanged])<= [Forms]![frmDateInput]![EndDate])
;

The criterion in the WHERE clause is simply a true or false expression: is the
field in the table ([DateOf Invoice] say) in fact greater or equal to the date
value in the textbox StartDate and less than or equal to the textbox EndDate?
If that statement is TRUE, return the record; if it's FALSE, don't return the
record. You need to understand that the words OR and AND are operators in
Boolean algebra, just like + and - are operators in arithmatic; X AND Y is a
TRUE statement if both X and Y are TRUE, it's false otherwise; X OR Y is a
TRUE statement if X is true, or if Y is true or if both are true, it's FALSE
only if both X and Y are false.

 

[John W. Vinson]

I've had a look at the coding you posted and with some minor tweaking to
include all my fields it works a treat.

I can't tell you what a relief it is to get this working.

Very glad to have been of assistance!

 

[hobbit2612 via AccessMonster.com]

Cheers take care!

Very glad to have been of assistance!

 

[hobbit2612 via AccessMonster.com]

John,

You very kindly gave me all the assistance with the problem I had on tracking
changes to my database fields which, worked a treat. But I've now noticed
that the checkboxes on the record now show that they have been changed when
they haven't.

I've narrowed this down to it happening when the record is created. So e.g.
when I add an Invoice Number for example the checkbox also shows it has been
updated.

I've tried a few things to sort this, changing the default value to 0, then
changing the checkbox physically on screen to a text box but this caused
further problems, but I just can't fathom it out.

Could you tell me please is there anyway to get around this.

Many thanks

Chris

 

[hobbit2612 via AccessMonster.com]

John,

Please ignore the last message I posetd. It was meant for the another John
that had helped me out with some other issues I had.

Apologies for messing you around.

Kind regards

Chris

John,

You very kindly gave me all the assistance with the problem I had on tracking
changes to my database fields which, worked a treat. But I've now noticed
that the checkboxes on the record now show that they have been changed when
they haven't.

I've narrowed this down to it happening when the record is created. So e.g.
when I add an Invoice Number for example the checkbox also shows it has been
updated.

I've tried a few things to sort this, changing the default value to 0, then
changing the checkbox physically on screen to a text box but this caused
further problems, but I just can't fathom it out.

Could you tell me please is there anyway to get around this.

Many thanks

Chris

Cheers take care!

[quoted text clipped - 4 lines]

 

[John W. Vinson]

You very kindly gave me all the assistance with the problem I had on tracking
changes to my database fields which, worked a treat. But I've now noticed
that the checkboxes on the record now show that they have been changed when
they haven't.

I've narrowed this down to it happening when the record is created. So e.g.
when I add an Invoice Number for example the checkbox also shows it has been
updated.

I've tried a few things to sort this, changing the default value to 0, then
changing the checkbox physically on screen to a text box but this caused
further problems, but I just can't fathom it out.

Could you tell me please is there anyway to get around this.

Not without some more information!

What's the Recordsource of the form? Post the SQL.
Which fields are getting set inappropriately?
What - if any - code or macros do you have in the Form's Current,
BeforeInsert, or BeforeUpdate event? What about the Invoice Number control's
AfterUpdate event?

 

[hobbit2612 via AccessMonster.com]

John, it's very good of you to offer some help, so many thanks

The subform where the problem occurs is linked directly to the table so there
is no SQL to post.

The fields that are getting set inapproriately are InvoicePaid and
ReportIssued.

There is no code in the OnCurrent event but there is the following in the
'BeforeUpdate event. I've cut this down because there are quite a lot of
fields some of which are commercially sensitive, but they all mirror the
coding with the difference being the field names.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.InvoiceNumber & "" <> Me.InvoiceNumber.OldValue & "" Then
Me.InvoiceNumberChanged = Date
End If
If Me.InvoicePaid & "" <> Me.InvoicePaid.OldValue & "" Then
Me.InvoicePaidChanged = Date
End If
If Me.ReportIssued & "" <> Me.ReportIssued.OldValue & "" Then
Me.ReportIssuedChanged = Date
End If
End Sub

In the AfterUpdate event of the InvoiceNumber there is a saverecord piece of
coding but even when I've disabled this the problem still occurs.

I hope this helps, and once again many thanks for the help.

Regards

Chris

You very kindly gave me all the assistance with the problem I had on tracking
changes to my database fields which, worked a treat. But I've now noticed

[quoted text clipped - 10 lines]

Could you tell me please is there anyway to get around this.

Not without some more information!

What's the Recordsource of the form? Post the SQL.
Which fields are getting set inappropriately?
What - if any - code or macros do you havein the Form's Current,
BeforeInsert, or BeforeUpdate event? What about the Invoice Number control's
AfterUpdate event?

 

[John W. Vinson]

There is no code in the OnCurrent event but there is the following in the
'BeforeUpdate event. I've cut this down because there are quite a lot of
fields some of which are commercially sensitive, but they all mirror the
coding with the difference being the field names.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.InvoiceNumber & "" <> Me.InvoiceNumber.OldValue & "" Then
Me.InvoiceNumberChanged = Date
End If
If Me.InvoicePaid & "" <> Me.InvoicePaid.OldValue & "" Then
Me.InvoicePaidChanged = Date
End If
If Me.ReportIssued & "" <> Me.ReportIssued.OldValue & "" Then
Me.ReportIssuedChanged = Date
End If
End Sub

In the AfterUpdate event of the InvoiceNumber there is a saverecord piece of
coding but even when I've disabled this the problem still occurs.

I hope this helps, and once again many thanks for the help.

On a new record, the OldValue will be NULL. If you don't want the record
datestamped when it's entered, check the form's NewRecord property:

If Not Me.NewRecord Then
<your if statements>
End If

 

[hobbit2612 via AccessMonster.com]

Hi John, thanks very much for replying.

I've been playing around with this and it doesn't seem to like the 'End If'
for this statement because of my other If Statements. It comes up with the
error 'Compile error: Block If without End If'.

Any ideas please

Regards

Chris

There is no code in the OnCurrent event but there is the following in the
'BeforeUpdate event. I've cut this down because there are quite a lot of

[quoted text clipped - 17 lines]

I hope this helps, and once again many thanks for the help.

On a new record, the OldValue will be NULL. If you don't want the record
datestamped when it's entered, check the form's NewRecord property:

If Not Me.NewRecord Then
<your if statements>
End If

 

[John W. Vinson]

Hi John, thanks very much for replying.

I've been playing around with this and it doesn't seem to like the 'End If'
for this statement because of my other If Statements. It comes up with the
error 'Compile error: Block If without End If'.

Any ideas please

Well... since you didn't (couldn't) post the actual code, all I can say is
that I'd be inclined to carefully pair up If and End If lines in your code. If
the compiler says there's a Block If without End If, I'd expect that it's
correct and that you have one more If line than you have End If.

 

[hobbit2612 via AccessMonster.com]

John,

Many thanks for the advice and for your time and trouble.

Regards

Chris

Hi John, thanks very much for replying.

[quoted text clipped - 3 lines]

Any ideas please

Well... since you didn't (couldn't) post the actual code, all I can say is
that I'd be inclined to carefully pair up If and End If lines in your code. If
the compiler says there's a Block If without End If, I'd expect that it's
correct and that you have one more If line than you have End If.