marking up two equal dates in a report? (Please read the message for better explanation) VBA ?

  • Thread starter Thread starter Lorenzo
  • Start date Start date
L

Lorenzo

Hello, I am trying to format a report in a way so that if I have two equal
dates I can somehow change the output of my report. I will first explain
what I am working on for a better understanding. This report is generated
by a query that has some imput parameters as arrival - departure dates. I
submit the dates and the report shows the list of arrivals for that timespan
I have chosen. In my report I have the following fields:

strReservationCode
dteArrival
strApartment
strNotes
....
....

I would like to be able to mark up for example changing the backColor
property when I have two arrivals on the same day:

Example: the report will have

resCode dteArr strApartment

03 01/01/2005 pippo
04 01/17/2005 pluto * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
05 01/17/2005 sempronio * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
06 01/01/2005 pippo

So far this is what I have ...


Sub controllaArrivi()
Dim lngRed As Long
lngRed = RGB(255, 0, 0)


Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim dteToCheckOne As Date
Dim dteToCheckTwo As Date
Dim strSQL As String

strSQL = "SELECT [dteDataArrivo] FROM qryReportArrivi " ' here I am not
considering the input parameter but should I?

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst

Do While True
With rs
dteToCheckOne= rs!dteDataArrivo
End With
rs.MoveNext
dteToCheckTwo= rs!dteDataArrivo

If dteDaControllareUno = dteDaControllareDue Then
Me.dteDataArrivo.BackStyle = lngRed
End If
Loop


Set db = Nothing


End Sub

? Help !
Lorenzo (Italia)
 
Lorenzo said:
Hello, I am trying to format a report in a way so that if I have two equal
dates I can somehow change the output of my report. I will first explain
what I am working on for a better understanding. This report is generated
by a query that has some imput parameters as arrival - departure dates. I
submit the dates and the report shows the list of arrivals for that timespan
I have chosen. In my report I have the following fields:

strReservationCode
dteArrival
strApartment
strNotes
...
...

I would like to be able to mark up for example changing the backColor
property when I have two arrivals on the same day:

Example: the report will have

resCode dteArr strApartment

03 01/01/2005 pippo
04 01/17/2005 pluto * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
05 01/17/2005 sempronio * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
06 01/01/2005 pippo
Click to expand...
[snip all the code]


This kind of thing should be handled in the report's record
source query. Add a calculated field with something like:

DtCnt: (SELECT Count(*) FROM table As X
WHERE dteArr Between [start] And [end]
AND X.dteArr = table.dteArr)

Then you can use Conditional Formatting to check if the
DtCnt field is >1 to make it red.
 
Hello Marshall,
many thanks for your kind reply and help. I have added the calculated field
as suggested and it works counting how many times an arrival occurs on the
same day. As it perfectly works in the query when I try to open the report
I get the follwoing error:

3612 Multi-level GROUP BY clause not
allowed in a subquery

I am not too good at reports then I must be doing somenthing wrong.
I have performed a quick search but I can't come up with a solution? Can
you give me a little extra help?
Lorenzo


Marshall Barton said:
Lorenzo said:
Hello, I am trying to format a report in a way so that if I have two
equal
dates I can somehow change the output of my report. I will first explain
what I am working on for a better understanding. This report is
generated
by a query that has some imput parameters as arrival - departure dates. I
submit the dates and the report shows the list of arrivals for that
timespan
I have chosen. In my report I have the following fields:

strReservationCode
dteArrival
strApartment
strNotes
...
...

I would like to be able to mark up for example changing the backColor
property when I have two arrivals on the same day:

Example: the report will have

resCode dteArr strApartment

03 01/01/2005 pippo
04 01/17/2005 pluto * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
05 01/17/2005 sempronio * backColor comes into play
and
tells me I have 2 arrivals on 01/17/2005
06 01/01/2005 pippo
Click to expand...
[snip all the code]


This kind of thing should be handled in the report's record
source query. Add a calculated field with something like:

DtCnt: (SELECT Count(*) FROM table As X
WHERE dteArr Between [start] And [end]
AND X.dteArr = table.dteArr)

Then you can use Conditional Formatting to check if the
DtCnt field is >1 to make it red.
Click to expand...
 
That problem happens when the report has something specified
in Sorting and Grouping or even if you just use an aggregate
function in the report.

I should have thought of that and used DCount instead of a
subquery:

DtCnt: DCount("*", "table", "dteArr Between " &
Format([start], "\#m\/d\/yyyy\#") & " And " & Format([end],
"\#m\/d\/yyyy\#") & " AND dteArr = " & Format(table.dteArr,
"\#m\/d\/yyyy\#"))

In looking at how messy that is, I wonder if it's really
necessary to check if the date is within the range. I
believe this should work just as well:

DtCnt: DCount("*", "table", "dteArr = " &
Format(table.dteArr, "\#m\/d\/yyyy\#"))
--
Marsh
MVP [MS Access]

many thanks for your kind reply and help. I have added the calculated field
as suggested and it works counting how many times an arrival occurs on the
same day. As it perfectly works in the query when I try to open the report
I get the follwoing error:

3612 Multi-level GROUP BY clause not
allowed in a subquery

I am not too good at reports then I must be doing somenthing wrong.
I have performed a quick search but I can't come up with a solution? Can
you give me a little extra help?
Lorenzo

Lorenzo said:
Hello, I am trying to format a report in a way so that if I have two
equal
dates I can somehow change the output of my report. I will first explain
what I am working on for a better understanding. This report is
generated
by a query that has some imput parameters as arrival - departure dates. I
submit the dates and the report shows the list of arrivals for that
timespan
I have chosen. In my report I have the following fields:

strReservationCode
dteArrival
strApartment
strNotes
...
...

I would like to be able to mark up for example changing the backColor
property when I have two arrivals on the same day:

Example: the report will have

resCode dteArr strApartment

03 01/01/2005 pippo
04 01/17/2005 pluto * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
05 01/17/2005 sempronio * backColor comes into play
and
tells me I have 2 arrivals on 01/17/2005
06 01/01/2005 pippo
Click to expand...
[snip all the code]

"Marshall Barton" ha scritto
This kind of thing should be handled in the report's record
source query. Add a calculated field with something like:

DtCnt: (SELECT Count(*) FROM table As X
WHERE dteArr Between [start] And [end]
AND X.dteArr = table.dteArr)

Then you can use Conditional Formatting to check if the
DtCnt field is >1 to make it red.
Click to expand...
Click to expand...
 
Hello Marsh,
it's me again...I can't get it to work. This is what happens:

DtCnt: DCount("*";"tblPrenotazioni";"[dteDataArrivo]= " &
Format([tblPrenotazioni!dteDataArrivo];"Data in cifre"))

Changing the formatting it even returs all the records or none...I don't get
it.
The function should count all the reords that have the exact same date in
the arrival field but it does not. Can you give some extra advice?

Lorenzo


Marshall Barton said:
That problem happens when the report has something specified
in Sorting and Grouping or even if you just use an aggregate
function in the report.

I should have thought of that and used DCount instead of a
subquery:

DtCnt: DCount("*", "table", "dteArr Between " &
Format([start], "\#m\/d\/yyyy\#") & " And " & Format([end],
"\#m\/d\/yyyy\#") & " AND dteArr = " & Format(table.dteArr,
"\#m\/d\/yyyy\#"))

In looking at how messy that is, I wonder if it's really
necessary to check if the date is within the range. I
believe this should work just as well:

DtCnt: DCount("*", "table", "dteArr = " &
Format(table.dteArr, "\#m\/d\/yyyy\#"))
--
Marsh
MVP [MS Access]

many thanks for your kind reply and help. I have added the calculated
field
as suggested and it works counting how many times an arrival occurs on the
same day. As it perfectly works in the query when I try to open the
report
I get the follwoing error:

3612 Multi-level GROUP BY clause not
allowed in a subquery

I am not too good at reports then I must be doing somenthing wrong.
I have performed a quick search but I can't come up with a solution? Can
you give me a little extra help?
Lorenzo

Lorenzo wrote:
Hello, I am trying to format a report in a way so that if I have two
equal
dates I can somehow change the output of my report. I will first
explain
what I am working on for a better understanding. This report is
generated
by a query that has some imput parameters as arrival - departure dates.
I
submit the dates and the report shows the list of arrivals for that
timespan
I have chosen. In my report I have the following fields:

strReservationCode
dteArrival
strApartment
strNotes
...
...

I would like to be able to mark up for example changing the backColor
property when I have two arrivals on the same day:

Example: the report will have

resCode dteArr strApartment

03 01/01/2005 pippo
04 01/17/2005 pluto * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
05 01/17/2005 sempronio * backColor comes into play
and
tells me I have 2 arrivals on 01/17/2005
06 01/01/2005 pippo
[snip all the code]

"Marshall Barton" ha scritto
This kind of thing should be handled in the report's record
source query. Add a calculated field with something like:

DtCnt: (SELECT Count(*) FROM table As X
WHERE dteArr Between [start] And [end]
AND X.dteArr = table.dteArr)

Then you can use Conditional Formatting to check if the
DtCnt field is >1 to make it red.
Click to expand...
Click to expand...
Click to expand...
 
Two things wrong with your translation of my expression.

You need to use the format that I specified so the
expression is independent of your system settings.

You have syntax errors in the field reference.

Try this:

DtCnt: DCount("*";"tblPrenotazioni";"[dteDataArrivo]= " &
Format([tblPrenotazioni].[dteDataArrivo];"\#m\/d\/yyyy\#"))
--
Marsh
MVP [MS Access]


it's me again...I can't get it to work. This is what happens:

DtCnt: DCount("*";"tblPrenotazioni";"[dteDataArrivo]= " &
Format([tblPrenotazioni!dteDataArrivo];"Data in cifre"))

Changing the formatting it even returs all the records or none...I don't get
it.
The function should count all the reords that have the exact same date in
the arrival field but it does not. Can you give some extra advice?


"Marshall Barton"ha scritto
That problem happens when the report has something specified
in Sorting and Grouping or even if you just use an aggregate
function in the report.

I should have thought of that and used DCount instead of a
subquery:

DtCnt: DCount("*", "table", "dteArr Between " &
Format([start], "\#m\/d\/yyyy\#") & " And " & Format([end],
"\#m\/d\/yyyy\#") & " AND dteArr = " & Format(table.dteArr,
"\#m\/d\/yyyy\#"))

In looking at how messy that is, I wonder if it's really
necessary to check if the date is within the range. I
believe this should work just as well:

DtCnt: DCount("*", "table", "dteArr = " &
Format(table.dteArr, "\#m\/d\/yyyy\#"))

many thanks for your kind reply and help. I have added the calculated
field
as suggested and it works counting how many times an arrival occurs on the
same day. As it perfectly works in the query when I try to open the
report
I get the follwoing error:

3612 Multi-level GROUP BY clause not
allowed in a subquery

I am not too good at reports then I must be doing somenthing wrong.
I have performed a quick search but I can't come up with a solution? Can
you give me a little extra help?
Lorenzo


Lorenzo wrote:
Hello, I am trying to format a report in a way so that if I have two
equal
dates I can somehow change the output of my report. I will first
explain
what I am working on for a better understanding. This report is
generated
by a query that has some imput parameters as arrival - departure dates.
I
submit the dates and the report shows the list of arrivals for that
timespan
I have chosen. In my report I have the following fields:

strReservationCode
dteArrival
strApartment
strNotes
...
...

I would like to be able to mark up for example changing the backColor
property when I have two arrivals on the same day:

Example: the report will have

resCode dteArr strApartment

03 01/01/2005 pippo
04 01/17/2005 pluto * backColor comes into play and
tells me I have 2 arrivals on 01/17/2005
05 01/17/2005 sempronio * backColor comes into play
and
tells me I have 2 arrivals on 01/17/2005
06 01/01/2005 pippo
[snip all the code]


"Marshall Barton" ha scritto
This kind of thing should be handled in the report's record
source query. Add a calculated field with something like:

DtCnt: (SELECT Count(*) FROM table As X
WHERE dteArr Between [start] And [end]
AND X.dteArr = table.dteArr)

Then you can use Conditional Formatting to check if the
DtCnt field is >1 to make it red.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top