Syntax error in opening a report

[Khalil Handal]

Hi to All,
I have the folowing line of code:

DoCmd.OpenReport "rptMemberBorrowedBooks", view:=acViewPreview, Wherecondition:=mstrWHERE

where mstrWHERE is:

mstrWHERE = "fkMemberID = " & Me.cboSelectedPerson & varReturned & varDates

varReturned: Is a criteria for a field if it is Null or Not Null
varDates: Is an ORDER BY a fieldName (one of three fields)

Running the command button to print the report gives the following error message:
Error 3075: Syntax error (missing operator) in query expression 'fkMemberId = 42 AND [DateReturned] is not null Order by DueDate'



Any ideas please?
Khalil

[xps35]

There order by should not be there.

[CJ_London]

No benefit in using the order by, the report will ignore it - you need to set the order in the report

Suggest show the report recordsource

[Khalil Handal]

Hi,
Thank you both.
My mstrWHERE is like this: (removed the Order by)

mstrWHERE = "fkMemberID = " & Me.cboSelectedPerson & varReturned & varDates

I can see the report in the Print View.
How cna I set the order from the report to be based on he value of: varDates?

Khalil

[CJ_London]

Presume removing the order by solved your syntax error

it is set in the report properties and/or the group/sort/totals section at the bottom of the report design view

[Khalil Handal]

Hi,
My issue is that my sorting is based on a dateField selected by an option group value (fraDates) on a form; The fraDates has three radio buttons: Date Borrowed, Date Due and Date Returned (Three different fields).
The user will choose one of the three date fields to sort.

How can I tell the report to use the selected date field to be used for sorting when opening the report?
Is it possible to do this using the On Load or On Open Event procedure of the report? How can it be done?

Khalil

[Minty]

You could pass the sort order in using the OpenArgs option and process it in the reports open event?

[Khalil Handal]

Hi,
Thank you for the suggestion of using OpenArgs. I lake the experience of using the OpenArgs option; I did some reading about it.
The form has a command button to open the report;

Step 1:
I should define the string that will be used as the OpenArgs which is fraDates on the form.
What should I write here?

Step 2:
I should have some lines on the report Open event to PASS the OpenArgs Value.

Dim strArgs As Integer
    strArgs = FormName!OpenArgs

I not certain if I have the correct lines.

Step 3:
Use the command to open the report:

DoCmd.OpenReport "rptMemberBorrowedBooks", acViewPreview, , mstrWHERE1, , OpenArgs

Khalil

[Welshgasman]

Dim strArgs As Integer

Seriously, you have a variable called strArgs and define it is Integer ?

Way to go to confuse people. including yourself.

[Khalil Handal]

Sorry,
It should be defined as a string

[Welshgasman]

Hi,
Thank you for the suggestion of using OpenArgs. I lake the experience of using the OpenArgs option; I did some reading about it.
The form has a command button to open the report;

Step 1:
I should define the string that will be used as the OpenArgs which is fraDates on the form.
What should I write here?

Step 2:
I should have some lines on the report Open event to PASS the OpenArgs Value.

Dim strArgs As Integer
    strArgs = FormName!OpenArgs

I not certain if I have the correct lines.

Step 3:
Use the command to open the report:

DoCmd.OpenReport "rptMemberBorrowedBooks", acViewPreview, , mstrWHERE1, , OpenArgs

Khalil

https://learn.microsoft.com/en-us/of...form-or-report