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 |
#11
|
|||
|
|||
Please help in this dynamic query
and i asked what is the normal procedure ..(.i mean syntax ...) i should
follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & Forms!searchfrm!stidcmb & "" strSQL = Replace(strSQL, " & Forms!searchfrm!stidcmb & ", Forms!searchfrm!activitycmb) Set r1s = asDB.OpenRecordset(strSQL) Me.RecordSource = strSQL r1s.MoveFirst With r1s Do While Not .EOF Me.Text12.ControlSource = r1s("totals") r1s.MoveNext Loop End With kindly help me ....i spent days ..no use .....thanks |
#12
|
|||
|
|||
Please help in this dynamic query
I expect you are having an issue with this line:
Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & Forms!searchfrm!stidcmb & "" strSQL = Replace(strSQL, " & Forms!searchfrm!stidcmb & ", Forms!searchfrm!activitycmb) Set r1s = asDB.OpenRecordset(strSQL) Me.RecordSource = strSQL r1s.MoveFirst With r1s Do While Not .EOF Me.Text12.ControlSource = r1s("totals") r1s.MoveNext Loop End With kindly help me ....i spent days ..no use .....thanks |
#13
|
|||
|
|||
Please help in this dynamic query
Dear sir....kindly look into this....
This is because of my table struct....i have fields like this stid stname dateofS Petrol diesel motel oil restaurant store ... ... so if i want to make report monthly,yearly... depends upon the selection of the user like month,year,petrol or diesel or motel.....and station1 or station2 or station3....etc........i have to make many queries and many reports is not it ? Thanks for your time "Duane Hookom" wrote: I expect you are having an issue with this line: Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & Forms!searchfrm!stidcmb & "" strSQL = Replace(strSQL, " & Forms!searchfrm!stidcmb & ", Forms!searchfrm!activitycmb) Set r1s = asDB.OpenRecordset(strSQL) Me.RecordSource = strSQL r1s.MoveFirst With r1s Do While Not .EOF Me.Text12.ControlSource = r1s("totals") r1s.MoveNext Loop End With kindly help me ....i spent days ..no use .....thanks |
#14
|
|||
|
|||
Please help in this dynamic query
Consider normalizing your table and you would not have an issue. Petrol,
Diesel, Motel,... should all be values in a field and not a field name. You could probably get by with one query and one report and absolutely no code if your table structure was normalized. If you can't or won't normalize, you should consider using a UNION query to normalize your table: SELECT stid, stname, dateOfS, Petrol as TheValue, "Petrol" as Category FROM details UNION ALL SELECT stid, stname, dateOfS, Diesel, "Diesel" FROM details UNION ALL SELECT stid, stname, dateOfS, Motel, "Motel" FROM details UNION ALL ---etc--- -- Duane Hookom MS Access MVP "ismail" wrote in message ... Dear sir....kindly look into this.... This is because of my table struct....i have fields like this stid stname dateofS Petrol diesel motel oil restaurant store .. .. so if i want to make report monthly,yearly... depends upon the selection of the user like month,year,petrol or diesel or motel.....and station1 or station2 or station3....etc........i have to make many queries and many reports is not it ? Thanks for your time "Duane Hookom" wrote: I expect you are having an issue with this line: Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & Forms!searchfrm!stidcmb & "" strSQL = Replace(strSQL, " & Forms!searchfrm!stidcmb & ", Forms!searchfrm!activitycmb) Set r1s = asDB.OpenRecordset(strSQL) Me.RecordSource = strSQL r1s.MoveFirst With r1s Do While Not .EOF Me.Text12.ControlSource = r1s("totals") r1s.MoveNext Loop End With kindly help me ....i spent days ..no use .....thanks |
#15
|
|||
|
|||
Please help in this dynamic query
....i hope this will do ....i will come back to you after i check...i hope
this will do...??? many thanks "Duane Hookom" wrote: Consider normalizing your table and you would not have an issue. Petrol, Diesel, Motel,... should all be values in a field and not a field name. You could probably get by with one query and one report and absolutely no code if your table structure was normalized. If you can't or won't normalize, you should consider using a UNION query to normalize your table: SELECT stid, stname, dateOfS, Petrol as TheValue, "Petrol" as Category FROM details UNION ALL SELECT stid, stname, dateOfS, Diesel, "Diesel" FROM details UNION ALL SELECT stid, stname, dateOfS, Motel, "Motel" FROM details UNION ALL ---etc--- -- Duane Hookom MS Access MVP "ismail" wrote in message ... Dear sir....kindly look into this.... This is because of my table struct....i have fields like this stid stname dateofS Petrol diesel motel oil restaurant store .. .. so if i want to make report monthly,yearly... depends upon the selection of the user like month,year,petrol or diesel or motel.....and station1 or station2 or station3....etc........i have to make many queries and many reports is not it ? Thanks for your time "Duane Hookom" wrote: I expect you are having an issue with this line: Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & Forms!searchfrm!stidcmb & "" strSQL = Replace(strSQL, " & Forms!searchfrm!stidcmb & ", Forms!searchfrm!activitycmb) Set r1s = asDB.OpenRecordset(strSQL) Me.RecordSource = strSQL r1s.MoveFirst With r1s Do While Not .EOF Me.Text12.ControlSource = r1s("totals") r1s.MoveNext Loop End With kindly help me ....i spent days ..no use .....thanks |
#16
|
|||
|
|||
Please help in this dynamic query
so i will get petrol, diesel,motel,restarnt .....values in one column and
categories petrol,diesel,motel,restarnt... in other column by using category value i have to select the value of petrol...diesel.....etc am i correct? Please reply.... Thanks "ismail" wrote: ...i hope this will do ....i will come back to you after i check...i hope this will do...??? many thanks "Duane Hookom" wrote: Consider normalizing your table and you would not have an issue. Petrol, Diesel, Motel,... should all be values in a field and not a field name. You could probably get by with one query and one report and absolutely no code if your table structure was normalized. If you can't or won't normalize, you should consider using a UNION query to normalize your table: SELECT stid, stname, dateOfS, Petrol as TheValue, "Petrol" as Category FROM details UNION ALL SELECT stid, stname, dateOfS, Diesel, "Diesel" FROM details UNION ALL SELECT stid, stname, dateOfS, Motel, "Motel" FROM details UNION ALL ---etc--- -- Duane Hookom MS Access MVP "ismail" wrote in message ... Dear sir....kindly look into this.... This is because of my table struct....i have fields like this stid stname dateofS Petrol diesel motel oil restaurant store .. .. so if i want to make report monthly,yearly... depends upon the selection of the user like month,year,petrol or diesel or motel.....and station1 or station2 or station3....etc........i have to make many queries and many reports is not it ? Thanks for your time "Duane Hookom" wrote: I expect you are having an issue with this line: Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & Forms!searchfrm!stidcmb & "" strSQL = Replace(strSQL, " & Forms!searchfrm!stidcmb & ", Forms!searchfrm!activitycmb) Set r1s = asDB.OpenRecordset(strSQL) Me.RecordSource = strSQL r1s.MoveFirst With r1s Do While Not .EOF Me.Text12.ControlSource = r1s("totals") r1s.MoveNext Loop End With kindly help me ....i spent days ..no use .....thanks |
#17
|
|||
|
|||
Please help in this dynamic query
Your union query results would have fields/columns of stid, stname, dateOfS,
TheValue, Category. To find all the Petrol values, your query would look like: SELECT * FROM quniYourUnionQuery WHERE Category = "Petrol"; If you want Petrol and Motel, use: SELECT * FROM quniYourUnionQuery WHERE Category IN ("Petrol","Motel"); -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... so i will get petrol, diesel,motel,restarnt .....values in one column and categories petrol,diesel,motel,restarnt... in other column by using category value i have to select the value of petrol...diesel.....etc am i correct? Please reply.... Thanks "ismail" wrote: ...i hope this will do ....i will come back to you after i check...i hope this will do...??? many thanks "Duane Hookom" wrote: Consider normalizing your table and you would not have an issue. Petrol, Diesel, Motel,... should all be values in a field and not a field name. You could probably get by with one query and one report and absolutely no code if your table structure was normalized. If you can't or won't normalize, you should consider using a UNION query to normalize your table: SELECT stid, stname, dateOfS, Petrol as TheValue, "Petrol" as Category FROM details UNION ALL SELECT stid, stname, dateOfS, Diesel, "Diesel" FROM details UNION ALL SELECT stid, stname, dateOfS, Motel, "Motel" FROM details UNION ALL ---etc--- -- Duane Hookom MS Access MVP "ismail" wrote in message ... Dear sir....kindly look into this.... This is because of my table struct....i have fields like this stid stname dateofS Petrol diesel motel oil restaurant store .. .. so if i want to make report monthly,yearly... depends upon the selection of the user like month,year,petrol or diesel or motel.....and station1 or station2 or station3....etc........i have to make many queries and many reports is not it ? Thanks for your time "Duane Hookom" wrote: I expect you are having an issue with this line: Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & Forms!searchfrm!stidcmb & "" strSQL = Replace(strSQL, " & Forms!searchfrm!stidcmb & ", Forms!searchfrm!activitycmb) Set r1s = asDB.OpenRecordset(strSQL) Me.RecordSource = strSQL r1s.MoveFirst With r1s Do While Not .EOF Me.Text12.ControlSource = r1s("totals") r1s.MoveNext Loop End With kindly help me ....i spent days ..no use .....thanks |
#18
|
|||
|
|||
Please help in this dynamic query
East or West You R BEST
Many Thanks "Duane Hookom" wrote: Your union query results would have fields/columns of stid, stname, dateOfS, TheValue, Category. To find all the Petrol values, your query would look like: SELECT * FROM quniYourUnionQuery WHERE Category = "Petrol"; If you want Petrol and Motel, use: SELECT * FROM quniYourUnionQuery WHERE Category IN ("Petrol","Motel"); -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... so i will get petrol, diesel,motel,restarnt .....values in one column and categories petrol,diesel,motel,restarnt... in other column by using category value i have to select the value of petrol...diesel.....etc am i correct? Please reply.... Thanks "ismail" wrote: ...i hope this will do ....i will come back to you after i check...i hope this will do...??? many thanks "Duane Hookom" wrote: Consider normalizing your table and you would not have an issue. Petrol, Diesel, Motel,... should all be values in a field and not a field name. You could probably get by with one query and one report and absolutely no code if your table structure was normalized. If you can't or won't normalize, you should consider using a UNION query to normalize your table: SELECT stid, stname, dateOfS, Petrol as TheValue, "Petrol" as Category FROM details UNION ALL SELECT stid, stname, dateOfS, Diesel, "Diesel" FROM details UNION ALL SELECT stid, stname, dateOfS, Motel, "Motel" FROM details UNION ALL ---etc--- -- Duane Hookom MS Access MVP "ismail" wrote in message ... Dear sir....kindly look into this.... This is because of my table struct....i have fields like this stid stname dateofS Petrol diesel motel oil restaurant store .. .. so if i want to make report monthly,yearly... depends upon the selection of the user like month,year,petrol or diesel or motel.....and station1 or station2 or station3....etc........i have to make many queries and many reports is not it ? Thanks for your time "Duane Hookom" wrote: I expect you are having an issue with this line: Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & |
#19
|
|||
|
|||
Please help in this dynamic query
Glad to be of assistance.
-- Duane Hookom MS Access MVP -- "ismail" wrote in message ... East or West You R BEST Many Thanks "Duane Hookom" wrote: Your union query results would have fields/columns of stid, stname, dateOfS, TheValue, Category. To find all the Petrol values, your query would look like: SELECT * FROM quniYourUnionQuery WHERE Category = "Petrol"; If you want Petrol and Motel, use: SELECT * FROM quniYourUnionQuery WHERE Category IN ("Petrol","Motel"); -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... so i will get petrol, diesel,motel,restarnt .....values in one column and categories petrol,diesel,motel,restarnt... in other column by using category value i have to select the value of petrol...diesel.....etc am i correct? Please reply.... Thanks "ismail" wrote: ...i hope this will do ....i will come back to you after i check...i hope this will do...??? many thanks "Duane Hookom" wrote: Consider normalizing your table and you would not have an issue. Petrol, Diesel, Motel,... should all be values in a field and not a field name. You could probably get by with one query and one report and absolutely no code if your table structure was normalized. If you can't or won't normalize, you should consider using a UNION query to normalize your table: SELECT stid, stname, dateOfS, Petrol as TheValue, "Petrol" as Category FROM details UNION ALL SELECT stid, stname, dateOfS, Diesel, "Diesel" FROM details UNION ALL SELECT stid, stname, dateOfS, Motel, "Motel" FROM details UNION ALL ---etc--- -- Duane Hookom MS Access MVP "ismail" wrote in message ... Dear sir....kindly look into this.... This is because of my table struct....i have fields like this stid stname dateofS Petrol diesel motel oil restaurant store .. .. so if i want to make report monthly,yearly... depends upon the selection of the user like month,year,petrol or diesel or motel.....and station1 or station2 or station3....etc........i have to make many queries and many reports is not it ? Thanks for your time "Duane Hookom" wrote: I expect you are having an issue with this line: Me.Text12.ControlSource = r1s("totals") Would you expect the value of [Totals] in the record set to be a number like 2232.45? Your code is setting the control source to a number when it should either: - set the Value to a number Me.Text12.Value = r1s("totals") - set the control source to a field name from the report's record soru Me.Text12.ControlSource = "[totals]" I still don't understand why you don't just bind your report to a saved query. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... and i asked what is the normal procedure ..(.i mean syntax ...) i should follow.. please i am waiting for ur reply. many thanks "Duane Hookom" wrote: Do you have a field named "2232.45"? I doubt this is possible since the period is not allowed in field/column names. -- Duane Hookom MS Access MVP -- "ismail" wrote in message ... I think it is not possible ? is it? "ismail" wrote: The message box is "Enter Parameter value" ----Title 2232.45 - value of the field which i want to display in the report - Empty text box ok cancel and what is right way or syntax to assign recordsource to report in open event of report..i create recordset in form and how can i pass to report..or how can i assign to report..please kindly help me Thanks "Duane Hookom" wrote: You have never told us what the parameter prompt message is. You can set the Record Source property of a report on the Open event of the report. You shouldn't need to create a recordset. -- Duane Hookom MS Access MVP "ismail" wrote in message ... I see there is no bound control or sorting on this field... please give me instruction what to do...? i just want to show the output in the report...i am doing this first time...(calling report and assigning recordsource through vba...)...please change my code according the right way..to do this.. Thanks "Duane Hookom" wrote: There is probably a control or sorting level bound to a field that isn't in the record source. If you can't figure this out, consider providing information on why you are opening a recordset in a report. -- Duane Hookom MS Access MVP "ismail" wrote in message ... yes i did as you told...... SELECT Store,dateofS,totalS ,stid FROM details where stid=1 this is the value and if i run this in new blank query..i get the result... please reply...Thanks "Duane Hookom" wrote: Try place a breakpoint prior to or on this line Set r1s = asDB.OpenRecordset(strSQL) When the code breaks, open the debug window (press Ctrl+G) and enter ? strSQL Copy the value of strSQL and paste it into a new blank query. Let us know what happens. -- Duane Hookom MS Access MVP "ismail" wrote in message ... This is the code i have in report open event..... what is the problem...i get "enter parameter " message when run the report Dim asDB As Database Dim r1s As Recordset Dim strSQL As String Dim d8, d9 As Variant Set asDB = CurrentDb d8 = Forms!searchfrm!activitycmb strSQL = "SELECT " & d8 & ",dateofS,totalS ,stid FROM details where stid=" & |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
AHHHH-Get Data from Multiple Excel workbooks | JAA149 | General Discussion | 5 | October 30th, 2005 05:19 PM |
Crosstab Query Help | Becks | New Users | 17 | October 11th, 2005 08:31 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |