If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Some help please.
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
aaa wrote:
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. 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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
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]; "Marshall Barton" wrote in message 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. -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
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" wrote in message ... 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]; "Marshall Barton" wrote in message 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. -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
aaa wrote:
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 & "'" 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. -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
aaa wrote:
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. 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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|