Some help please.

  • Thread starter Thread starter aaa
  • Start date Start date
A

aaa

I don't do a lot of Access programming so I was hoping to get some help from
Access guru's on a couple of small issues.

1. How do I format Access report fields, ie: I have a zero coming over that
needs to be a yes/no also a date/time but I only want the date.

I thought there would be some formating I could put on the field like
Crystal but that doesn't seem to be the case

2. I have a couple of reports that I am calling from a form and they are
prompting me for "Enter Parameter Value"

I know there is a param on the report that it doesn't recognize. On one the
param name is "buffer". I have looked all up and down the query and there is
no field named "buffer". Another keeps asking for a param that is in the
query ( which has a ton of sums and is built from other queries) but when I
send the param from the form in the criteria it doesn't like it and gives me
the dialog.

Any help is appreciated.
 
1. Yes/no Data - Use an If...Then statement. (i.e.
iif([yes/nofield]=0,"No","Yes") -
Date/Time Format - format date/time using
format([date/timefieldname],"m/d/yy")
Also, make sure that the control name is different from the field you are
formatting (i.e - if you are using the if...then example above, make sure
the name of the control in the report is not [yes/nofield].
2. If you used any formulas, filters, or sorting make sure the refernce to
the fields are exactly the same name as the original field
 
aaa said:
I don't do a lot of Access programming so I was hoping to get some help from
Access guru's on a couple of small issues.

1. How do I format Access report fields, ie: I have a zero coming over that
needs to be a yes/no also a date/time but I only want the date.

I thought there would be some formating I could put on the field like
Crystal but that doesn't seem to be the case

2. I have a couple of reports that I am calling from a form and they are
prompting me for "Enter Parameter Value"

I know there is a param on the report that it doesn't recognize. On one the
param name is "buffer". I have looked all up and down the query and there is
no field named "buffer". Another keeps asking for a param that is in the
query ( which has a ton of sums and is built from other queries) but when I
send the param from the form in the criteria it doesn't like it and gives me
the dialog.
Click to expand...


I have no idea what you can or can't do with Crystal
Reports, but text box controls in an Access form/report have
a Format property where you can specify all kind of custom
formatting rules. There is a predefined format, "Yes/No",
that you can select from the property's drop down list.
THere are also several predefined date formats to choose
from.

If you want a custom format that goes beyond the basic
predefined ones, then look up Format Property in Help for an
extensive list of formatting codes that can be combined in
many ways to provide you with wide variety of custom
formats.

A parameter prompt from a query, means there is something in
the query that Access can't find elsewhere in the query or
in an **open** form/report.

A parameter prompt from a report may be from the report's
record source query, from a report control or an entry in
the Sorting and Grouping field list.
 
Yes but I am entering the exact name of the param in my call to the report
just as I do all of the other calls and it just won't recognize it. Here is
the call and the query:

Dim criteriaString
DoCmd.Close acReport, "MyReport"
'param by year
If Not IsNull(Me.Text5) And Me.Text5 > 0 Then
If Len(criteriaString) > 0 Then
criteriaString = criteriaString & " and [paramYear]= '" &
Me.Text5 & "'"
Else
criteriaString = "[paramYear]= '" & Me.Text5 & "'"
End If
End If

If Len(criteriaString) > 0 Then
DoCmd.OpenReport "MyReport", acViewPreview, , "[paramYear]= '" &
Me.Text5 & "'"
Else
DoCmd.OpenReport "MyReport", acViewPreview
End If


SELECT [SumOfSumOfJan1] & ' / ' & [SumOfSumOfJan] AS Jan, [SumOfSumOfFeb1] &
' / ' & [SumOfSumOfFeb] AS Feb, [SumOfSumOfMar1] & ' / ' & [SumOfSumOfMar]
AS Mar, [SumOfSumOfApr1] & ' / ' & [SumOfSumOfApr] AS Apr, [SumOfSumOfMay1]
& ' / ' & [SumOfSumOfMay] AS May, [SumOfSumOfJun1] & ' / ' & [SumOfSumOfJun]
AS Jun, [SumOfSumOfJul1] & ' / ' & [SumOfSumOfJul] AS Jul, [SumOfSumOfAug1]
& ' / ' & [SumOfSumOfAug] AS Aug, [SumOfSumOfSep1] & ' / ' & [SumOfSumOfSep]
AS Sep, [SumOfSumOfOct1] & ' / ' & [SumOfSumOfOct] AS Oct, [SumOfSumOfNov1]
& ' / ' & [SumOfSumOfNov] AS Nov, [SumOfSumOfDec1] & ' / ' & [SumOfSumOfDec]
AS [Dec],
[SumOfSumOfJan1]+[SumOfSumOfFeb1]+[SumOfSumOfMar1]+[SumOfSumOfApr1]+[SumOfSu
mOfFeb1]+[SumOfSumOfMay1]+[SumOfSumOfJun1]+[SumOfSumOfJul1]+[SumOfSumOfAug1]
+[SumOfSumOfSep1]+[SumOfSumOfOct1]+[SumOfSumOfNov1]+[SumOfSumOfDec1] AS
SessionTotal,
[SumOfSumOfJan]+[SumOfSumOfFeb]+[SumOfSumOfMar]+[SumOfSumOfApr]+[SumOfSumOfF
eb]+[SumOfSumOfMay]+[SumOfSumOfJun]+[SumOfSumOfJul]+[SumOfSumOfAug]+[SumOfSu
mOfSep]+[SumOfSumOfOct]+[SumOfSumOfNov]+[SumOfSumOfDec] AS EventTotal,
[SessionTotal] & ' / ' & [EventTotal] AS Total, [paramYear] AS paramYear
FROM [Events To Date By Employer Grand Totals Query];
 
I don't know if this is the problem but there are 3 sub queries bing called
by one main query. They all want a parameter called paramYear and I am
passing it from the form yet I am still getting prompted. The report runs
fine. I just need a way to send the param it needs without getting prompted.



aaa said:
Yes but I am entering the exact name of the param in my call to the report
just as I do all of the other calls and it just won't recognize it. Here is
the call and the query:

Dim criteriaString
DoCmd.Close acReport, "MyReport"
'param by year
If Not IsNull(Me.Text5) And Me.Text5 > 0 Then
If Len(criteriaString) > 0 Then
criteriaString = criteriaString & " and [paramYear]= '" &
Me.Text5 & "'"
Else
criteriaString = "[paramYear]= '" & Me.Text5 & "'"
End If
End If

If Len(criteriaString) > 0 Then
DoCmd.OpenReport "MyReport", acViewPreview, , "[paramYear]= '" &
Me.Text5 & "'"
Else
DoCmd.OpenReport "MyReport", acViewPreview
End If


SELECT [SumOfSumOfJan1] & ' / ' & [SumOfSumOfJan] AS Jan, [SumOfSumOfFeb1] &
' / ' & [SumOfSumOfFeb] AS Feb, [SumOfSumOfMar1] & ' / ' & [SumOfSumOfMar]
AS Mar, [SumOfSumOfApr1] & ' / ' & [SumOfSumOfApr] AS Apr, [SumOfSumOfMay1]
& ' / ' & [SumOfSumOfMay] AS May, [SumOfSumOfJun1] & ' / ' & [SumOfSumOfJun]
AS Jun, [SumOfSumOfJul1] & ' / ' & [SumOfSumOfJul] AS Jul, [SumOfSumOfAug1]
& ' / ' & [SumOfSumOfAug] AS Aug, [SumOfSumOfSep1] & ' / ' & [SumOfSumOfSep]
AS Sep, [SumOfSumOfOct1] & ' / ' & [SumOfSumOfOct] AS Oct, [SumOfSumOfNov1]
& ' / ' & [SumOfSumOfNov] AS Nov, [SumOfSumOfDec1] & ' / ' & [SumOfSumOfDec]
AS [Dec],
[SumOfSumOfJan1]+[SumOfSumOfFeb1]+[SumOfSumOfMar1]+[SumOfSumOfApr1]+[SumOfSumOfFeb1]+[SumOfSumOfMay1]+[SumOfSumOfJun1]+[SumOfSumOfJul1]+[SumOfSumOfAug1]
+[SumOfSumOfSep1]+[SumOfSumOfOct1]+[SumOfSumOfNov1]+[SumOfSumOfDec1] AS
SessionTotal,
[SumOfSumOfJan]+[SumOfSumOfFeb]+[SumOfSumOfMar]+[SumOfSumOfApr]+[SumOfSumOfFeb]+[SumOfSumOfMay]+[SumOfSumOfJun]+[SumOfSumOfJul]+[SumOfSumOfAug]+[SumOfSu
mOfSep]+[SumOfSumOfOct]+[SumOfSumOfNov]+[SumOfSumOfDec] AS EventTotal,
[SessionTotal] & ' / ' & [EventTotal] AS Total, [paramYear] AS paramYear
FROM [Events To Date By Employer Grand Totals Query];
Click to expand...
 
aaa said:
Yes but I am entering the exact name of the param in my call to the report
just as I do all of the other calls and it just won't recognize it. Here is
the call and the query:

Dim criteriaString
DoCmd.Close acReport, "MyReport"
'param by year
If Not IsNull(Me.Text5) And Me.Text5 > 0 Then
If Len(criteriaString) > 0 Then
criteriaString = criteriaString & " and [paramYear]= '" &
Me.Text5 & "'"
Else
criteriaString = "[paramYear]= '" & Me.Text5 & "'"
End If
End If

If Len(criteriaString) > 0 Then
DoCmd.OpenReport "MyReport", acViewPreview, , "[paramYear]= '" &
Me.Text5 & "'"
Click to expand...


That does not "pass" the parameter to the query. It does
provide a filter that is used to restrict the records that
the report will see.

If you would remove paramYear from the query, you would see
that effect.
 
aaa said:
I don't know if this is the problem but there are 3 sub queries bing called
by one main query. They all want a parameter called paramYear and I am
passing it from the form yet I am still getting prompted. The report runs
fine. I just need a way to send the param it needs without getting prompted.
Click to expand...


Ahh, yes, that will definitely cause multiple prompts for
the parameter's value.

The way to deal with all these issues is to place the
parameter's value in a text box on an **open** form and
change the parameter in all the queries to refer to the text
box. E.g. Form1!theformname.thetextboxname.
 
Back
Top