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

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

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

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

....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

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

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

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="
&

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="
&