G
Guest
how can i add the result of a query that my report is not based on into the
report header
report header
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Ok, I remember now, Uke. I had suggested a query, but you couldn't update
the check box. in the subform where the query was record source. That was
the issue I was going to work on. Okay, got it. I will get it done by
tomorrow afternoon, and I promise not to loose this link again.
the check box. in the subform where the query was record source. That was
the issue I was going to work on. Okay, got it. I will get it done by
tomorrow afternoon, and I promise not to loose this link again.
G
Guest
Missing a Quote Mark
v
=DLookup("[Years]" & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
--
Dave Hargis, Microsoft Access MVP
v
=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
=DLookup("[Years]" & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
--
Dave Hargis, Microsoft Access MVP
The Uke said:still getting a syntax error
=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
I changed # to Num and year to years
Double checked the spelling and still
Klatuu said:I thought this was for the header of a report?
If so, it goes on the report in the Control Source property of a text box.
If the year value is on an open form, then you need to fully qualify the
reference
Couple of points
# should not be used in names
Year is a reserved word in Access and will cause problems when used as a name.
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays", "[Year] = " &
[Forms]![frmfinalreport]!txtTheYear)
--
Dave Hargis, Microsoft Access MVP
The Uke said:tablename is #ofworkdays
the fields are Year, Workdays
Query name is qry#ofworkdays
sql view of query
SELECT [#ofworkdays].Workdays
FROM [#ofworkdays]
WHERE ((([#ofworkdays].Year)=[Forms]![frmfinalreport]![year]));
so as far is i under stand it will look something like this
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays",
"[Year] = " & Me.txtTheYear)
puting this in the form didn't work I get a syntax error
Question? does this need to change Me.txtTheYear
:
Actually, you can return both with the DLookup
=DLookup("[TheYearField] & " - " & "[TheDaysField]", "TheTableName",
"[TheYearField] = " & Me.txtTheYear)
Put the year in a text box on your report or you can refer to it on a form
is the form is open.
--
Dave Hargis, Microsoft Access MVP
:
I have a unbound txt box with the year in a form attached to a query as a
critria the result of the query is the number of days avabiable to work in
that year
which is located in the table that has the year and the #of days
:
How many rows and columns do you expect the query to return?
--
Dave Hargis, Microsoft Access MVP
:
how can i add the result of a query that my report is not based on into the
report header
G
Guest
That I can't help with without know the data in your table and the value in
the Years text box on the form.
As an alternative, here is a function that calculates the number of workdays
between two dates. It counts the raw number of day, subtracts Sundays and
Mondays, and then subtracts Holidays. To identify holidays, you need a two
column table that has the date of the holiday and a description. You don't
really have to have the description for this function, but it is nice to have
when looking up info in the table and if you need to use it for other
purposes.
The table name is Holidays and the date field is named [holdate]
Put the code in a standard module but Do Not name the module the same as the
function, that causes errors.
To call it, you pass it first date and the last date.
For your report, you can call it from the Control Source of the text box:
=CalcWorkDays([Forms]![frmfinalreport]!FirstDay,
[Forms]![frmfinalreport]!LastDay)
If I knew what you were putting in [Forms]![frmfinalreport]!Years),
I think I could show you a way to use that instead of having to have the
user enter the two dates.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays tabl
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
the Years text box on the form.
As an alternative, here is a function that calculates the number of workdays
between two dates. It counts the raw number of day, subtracts Sundays and
Mondays, and then subtracts Holidays. To identify holidays, you need a two
column table that has the date of the holiday and a description. You don't
really have to have the description for this function, but it is nice to have
when looking up info in the table and if you need to use it for other
purposes.
The table name is Holidays and the date field is named [holdate]
Put the code in a standard module but Do Not name the module the same as the
function, that causes errors.
To call it, you pass it first date and the last date.
For your report, you can call it from the Control Source of the text box:
=CalcWorkDays([Forms]![frmfinalreport]!FirstDay,
[Forms]![frmfinalreport]!LastDay)
If I knew what you were putting in [Forms]![frmfinalreport]!Years),
I think I could show you a way to use that instead of having to have the
user enter the two dates.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays tabl
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
The Uke said:good catch the syntax is right but the number is wrong. The number that is
displaying is 797 but it needs to be 1210 i have no clue what hole it is
pulling that number out of
Klatuu said:Missing a Quote Mark
v=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
=DLookup("[Years]" & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &[Forms]![frmfinalreport]!Years)
--
Dave Hargis, Microsoft Access MVP
The Uke said:still getting a syntax error
=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
I changed # to Num and year to years
Double checked the spelling and still
:
I thought this was for the header of a report?
If so, it goes on the report in the Control Source property of a text box.
If the year value is on an open form, then you need to fully qualify the
reference
Couple of points
# should not be used in names
Year is a reserved word in Access and will cause problems when used as a name.
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays", "[Year] = " &
[Forms]![frmfinalreport]!txtTheYear)
--
Dave Hargis, Microsoft Access MVP
:
tablename is #ofworkdays
the fields are Year, Workdays
Query name is qry#ofworkdays
sql view of query
SELECT [#ofworkdays].Workdays
FROM [#ofworkdays]
WHERE ((([#ofworkdays].Year)=[Forms]![frmfinalreport]![year]));
so as far is i under stand it will look something like this
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays",
"[Year] = " & Me.txtTheYear)
puting this in the form didn't work I get a syntax error
Question? does this need to change Me.txtTheYear
:
Actually, you can return both with the DLookup
=DLookup("[TheYearField] & " - " & "[TheDaysField]", "TheTableName",
"[TheYearField] = " & Me.txtTheYear)
Put the year in a text box on your report or you can refer to it on a form
is the form is open.
--
Dave Hargis, Microsoft Access MVP
:
I have a unbound txt box with the year in a form attached to a query as a
critria the result of the query is the number of days avabiable to work in
that year
which is located in the table that has the year and the #of days
:
How many rows and columns do you expect the query to return?
--
Dave Hargis, Microsoft Access MVP
:
how can i add the result of a query that my report is not based on into the
report header
G
Guest
I figure were its coming from well kinda Year - workdays 2007 - 1210 = 797
Klatuu said:Missing a Quote Mark
v=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
=DLookup("[Years]" & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &[Forms]![frmfinalreport]!Years)
--
Dave Hargis, Microsoft Access MVP
The Uke said:still getting a syntax error
=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
I changed # to Num and year to years
Double checked the spelling and still
Klatuu said:I thought this was for the header of a report?
If so, it goes on the report in the Control Source property of a text box.
If the year value is on an open form, then you need to fully qualify the
reference
Couple of points
# should not be used in names
Year is a reserved word in Access and will cause problems when used as a name.
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays", "[Year] = " &
[Forms]![frmfinalreport]!txtTheYear)
--
Dave Hargis, Microsoft Access MVP
:
tablename is #ofworkdays
the fields are Year, Workdays
Query name is qry#ofworkdays
sql view of query
SELECT [#ofworkdays].Workdays
FROM [#ofworkdays]
WHERE ((([#ofworkdays].Year)=[Forms]![frmfinalreport]![year]));
so as far is i under stand it will look something like this
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays",
"[Year] = " & Me.txtTheYear)
puting this in the form didn't work I get a syntax error
Question? does this need to change Me.txtTheYear
:
Actually, you can return both with the DLookup
=DLookup("[TheYearField] & " - " & "[TheDaysField]", "TheTableName",
"[TheYearField] = " & Me.txtTheYear)
Put the year in a text box on your report or you can refer to it on a form
is the form is open.
--
Dave Hargis, Microsoft Access MVP
:
I have a unbound txt box with the year in a form attached to a query as a
critria the result of the query is the number of days avabiable to work in
that year
which is located in the table that has the year and the #of days
:
How many rows and columns do you expect the query to return?
--
Dave Hargis, Microsoft Access MVP
:
how can i add the result of a query that my report is not based on into the
report header
G
Guest
Ok i will do my best to explain my problem maybe the best way to get at this
is to start agian.
I have a report that is based on a querty. there are two parameters for the
query one is the date which is done through a from with a date spread on it.
the second was the userID.
You suggestted making a checkbox subform to check off the users i wanted to
print that report for. so basically have a from the the date range in it, on
the side of it have check boxs for userids. Then have a button that would
loop through each of the users in the list print the report based on that
userid then go to the next person in the list untill the list as done. this
sound possiable and not really hard.
is to start agian.
I have a report that is based on a querty. there are two parameters for the
query one is the date which is done through a from with a date spread on it.
the second was the userID.
You suggestted making a checkbox subform to check off the users i wanted to
print that report for. so basically have a from the the date range in it, on
the side of it have check boxs for userids. Then have a button that would
loop through each of the users in the list print the report based on that
userid then go to the next person in the list untill the list as done. this
sound possiable and not really hard.
G
Guest
still getting a syntax error
=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
I changed # to Num and year to years
Double checked the spelling and still
=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
I changed # to Num and year to years
Double checked the spelling and still
Klatuu said:I thought this was for the header of a report?
If so, it goes on the report in the Control Source property of a text box.
If the year value is on an open form, then you need to fully qualify the
reference
Couple of points
# should not be used in names
Year is a reserved word in Access and will cause problems when used as a name.
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays", "[Year] = " &
[Forms]![frmfinalreport]!txtTheYear)
--
Dave Hargis, Microsoft Access MVP
The Uke said:tablename is #ofworkdays
the fields are Year, Workdays
Query name is qry#ofworkdays
sql view of query
SELECT [#ofworkdays].Workdays
FROM [#ofworkdays]
WHERE ((([#ofworkdays].Year)=[Forms]![frmfinalreport]![year]));
so as far is i under stand it will look something like this
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays",
"[Year] = " & Me.txtTheYear)
puting this in the form didn't work I get a syntax error
Question? does this need to change Me.txtTheYear
Klatuu said:Actually, you can return both with the DLookup
=DLookup("[TheYearField] & " - " & "[TheDaysField]", "TheTableName",
"[TheYearField] = " & Me.txtTheYear)
Put the year in a text box on your report or you can refer to it on a form
is the form is open.
--
Dave Hargis, Microsoft Access MVP
:
I have a unbound txt box with the year in a form attached to a query as a
critria the result of the query is the number of days avabiable to work in
that year
which is located in the table that has the year and the #of days
:
How many rows and columns do you expect the query to return?
--
Dave Hargis, Microsoft Access MVP
:
how can i add the result of a query that my report is not based on into the
report header
G
Guest
With out useing a subreport
G
Guest
I have a unbound txt box with the year in a form attached to a query as a
critria the result of the query is the number of days avabiable to work in
that year
which is located in the table that has the year and the #of days
critria the result of the query is the number of days avabiable to work in
that year
which is located in the table that has the year and the #of days
G
Guest
=DLookUp("[Workdays]","Numofworkdays","[Years] = " &
[Forms]![frmfinalreport]![Years])
corrected code erased the {"[Years] & " - " &} and I got the answer thanks
dave my faith in you and other mvps and been restored jk thanks for the help
[Forms]![frmfinalreport]![Years])
corrected code erased the {"[Years] & " - " &} and I got the answer thanks
dave my faith in you and other mvps and been restored jk thanks for the help
The Uke said:I figure were its coming from well kinda Year - workdays 2007 - 1210 = 797
Klatuu said:Missing a Quote Mark
v=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
=DLookup("[Years]" & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &[Forms]![frmfinalreport]!Years)
--
Dave Hargis, Microsoft Access MVP
The Uke said:still getting a syntax error
=DLookup("[Years] & " - " & "[Workdays]", "Numofworkdays", "[Years] = " &
[Forms]![frmfinalreport]!Years)
I changed # to Num and year to years
Double checked the spelling and still
:
I thought this was for the header of a report?
If so, it goes on the report in the Control Source property of a text box.
If the year value is on an open form, then you need to fully qualify the
reference
Couple of points
# should not be used in names
Year is a reserved word in Access and will cause problems when used as a name.
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays", "[Year] = " &
[Forms]![frmfinalreport]!txtTheYear)
--
Dave Hargis, Microsoft Access MVP
:
tablename is #ofworkdays
the fields are Year, Workdays
Query name is qry#ofworkdays
sql view of query
SELECT [#ofworkdays].Workdays
FROM [#ofworkdays]
WHERE ((([#ofworkdays].Year)=[Forms]![frmfinalreport]![year]));
so as far is i under stand it will look something like this
=DLookup("[Year] & " - " & "[Workdays]", "#ofworkdays",
"[Year] = " & Me.txtTheYear)
puting this in the form didn't work I get a syntax error
Question? does this need to change Me.txtTheYear
:
Actually, you can return both with the DLookup
=DLookup("[TheYearField] & " - " & "[TheDaysField]", "TheTableName",
"[TheYearField] = " & Me.txtTheYear)
Put the year in a text box on your report or you can refer to it on a form
is the form is open.
--
Dave Hargis, Microsoft Access MVP
:
I have a unbound txt box with the year in a form attached to a query as a
critria the result of the query is the number of days avabiable to work in
that year
which is located in the table that has the year and the #of days
:
How many rows and columns do you expect the query to return?
--
Dave Hargis, Microsoft Access MVP
:
how can i add the result of a query that my report is not based on into the
report header
G
Guest
1 thats all that should be returned
Klatuu said:How many rows and columns do you expect the query to return?
B
Bob Howard
How about putting the entire calculation in the report footer in a
non-visible control, and then refer to that control in the header.
non-visible control, and then refer to that control in the header.
G
Guest
I tried that before i even started but this works better
G
Guest
Just wanted to see if you came up with anything yet