On your report, add another text box. Change the name to txtValue, do not make it bound to any fields.

click on the detail section and then click on properties.
after this, go to the event tab, click on the on format event. Select code builder from the option, click ok.
paste the code and enter the values that I did not enter.

I still do not understand why you are not using a pas trough query to SQL Server. You would have been done by now. Lok up Pass trhough queries in access help.





Sal

thanks for your response.

I add another text box. Change the name to txtValue.

and then click on the detail section and then click on properties.
after this, I go to the event tab, I click on the on format event. Select code builder from the option and write the following coding
in code builder

Select Case True
Case posit = "Sr. Resident Engineer"
                txtvalue = 1
Case posit = "Resident Engineer"
                txtvalue = 2
Case posit = "Quantity Surveyor"
                txtvalue = 3
Case posit = "Inspector"
                txtvalue = 4
Case posit = "Inspector"
                txtvalue = 5
Case posit = "Surveyor"
                txtvalue = 6
Case posit = "Technician"
                txtvalue = 7

End Select


It was give syntax error when I use
Case posit = 'Sr. Resident Engineer'

single quotation marks. I change single quotation mark
to bouble quotation mark.
after that I run the report it give following error.

"Run time error 94
invalid use of NUL"

I could not understand where I am doing mistake.

If you never mind please send to me your e.mail address.
so that I could send you attach file (access database small)
only one report will save (employees)
Please check the coding and textbox name txtvalue. where I am doing mistake.
and resend to me. I will run on my pc.

(I use Access report becuase we could not make report on sql server 2000, my tables data are store in sql server 2000 which is link
with the access, so that we could develop report.)

Thanks for your cooperation.

Regards.

Mateen
[email protected]

order by is working.
just change your query, it display the records like following.

thanks.



SELECT [dbo_employees].[empno], [dbo_employees].[name], [dbo_employees].[posit]
FROM dbo_employees
ORDER BY IIf([posit]='Sr. Resident Engineer',1,
IIf([posit]='Resident Engineer',2,
IIf([posit]='Asst. Resident Engineer',3,
IIf([posit]='Quantity Surveyor',4,
IIf([posit]='Inspector',5,
IIf([posit]='Inspector (As Built)',6,
IIf([posit]='Surveyor',7,
IIf([posit]='Technician',8,
IIf([posit]='Secretary',9,
IIf([posit]='Clerk Expeditor',10,
IIf([posit]='Driver',11,
IIf([posit]='Party Chief',12,
IIf([posit]='Utility Engineer',13,14)))))))))))));




Mateen

enter another case line for any other values

case else
        txtvalue = "whatever"

or txtvalue = ""



Sal

thanks for response.
there is one problem, when I save following query it is not save, why ?
I could not understand why it is not save the query.
when I run this query it is runing but when I try to save the
Query it close the Access.

I have to use this query in the Access Report.
I have prepare report, which is showing contractno information
group by.

you can give me one example with the group by?
how can use following query in the Group by ?



SELECT [dbo_employees].[empno], [dbo_employees].[name], [dbo_approvals].[contractno], [dbo_assignments1].[aposit], [dbo_assignments1].[acontract], [dbo_assignments1].[astrdate], [dbo_assignments1].[aenddate]
FROM (dbo_employees INNER JOIN dbo_approvals ON [dbo_employees].[empno]=[dbo_approvals].[empno]) INNER JOIN dbo_assignments1 ON [dbo_employees].[empno]=[dbo_assignments1].[empno]
GROUP BY [dbo_approvals].[contractno], [dbo_employees].[empno], [dbo_employees].[name], [dbo_assignments1].[aposit], [dbo_assignments1].[acontract], [dbo_assignments1].[astrdate], [dbo_assignments1].[aenddate]
ORDER BY IIf( [dbo_assignments1].[aposit]='Sr. Resident Engineer',1,
IIf( [dbo_assignments1].[aposit]='Resident Engineer',2,
IIf( [dbo_assignments1].[aposit]='Asst. Resident Engineer',3,
IIf( [dbo_assignments1].[aposit]='Quantity Surveyor',4,
IIf( [dbo_assignments1].[aposit]='Inspector',5,
IIf( [dbo_assignments1].[aposit]='Inspector (As Built)',6,
IIf( [dbo_assignments1].[aposit]='Surveyor',7,
IIf( [dbo_assignments1].[aposit]='Technician',8,
IIf( [dbo_assignments1].[aposit]='Secretary',9,
IIf( [dbo_assignments1].[aposit]='Clerk Expeditor',10,
IIf( [dbo_assignments1].[aposit]='Driver',11,
IIf( [dbo_assignments1].[aposit]='Party Chief',12,
IIf( [dbo_assignments1].[aposit]='Utility Engineer',13,14)))))))))))));



Regards.

Mateen

Select Case True
 Case posit = "Sr. Resident Engineer"
                txtvalue = 1
  Case posit = "Resident Engineer"
                txtvalue = 2
  Case posit = "Quantity Surveyor"
                txtvalue = 3
  Case posit = "Inspector"
                txtvalue = 4
  Case posit = "Inspector"
                txtvalue = 5
  Case posit = "Surveyor"
                txtvalue = 6
  Case posit = "Technician"
                txtvalue = 7
  case else
                txtvalue = 0
End Select

Try that, access is finding a value that is null and does not have a case for null.



Sal

thanks for your response.

I am trying as per your instruction. I add text box on my report in details section.
and change the name to txtvalue.
after that I click on the detail section and then click on properties.
after this I go to the event tab, click on the form event, I select code builder from the option, and click ok.
in the code builder I paste the following code.

Select Case True
Case dbo_assignments1.aposit = "Sr. Resident Engineer"
                txtvalue = 1
Case dbo_assignments1.aposit = "Resident Engineer"
                txtvalue = 2
Case dbo_assignments1.aposit = "Asst. Resident Engineer"
                txtvalue = 3
Case dbo_assignments1.aposit = "Quantity Surveyor"
                txtvalue = 4
Case dbo_assignments1.aposit = "Inspector"
                txtvalue = 5
Case dbo_assignments1.aposit = "Inspector (As Built)"
                txtvalue = 6
Case dbo_assignments1.aposit = "Surveyor"
                txtvalue = 7
Case dbo_assignments1.aposit = "Technician"
                txtvalue = 8
Case dbo_assignments1.aposit = "Secretary"
                txtvalue = 9
Case dbo_assignments1.aposit = "Clerk / Expeditor"
                txtvalue = 10
Case dbo_assignments1.aposit = "Driver"
                txtvalue = 11
Case dbo_assignments1.aposit = "Party Chief"
                txtvalue = 12
Case Else
                txtvalue = 0
End Select


when I run the report it give following error.

" Run time error 424, object required "

in the code builder following line

Case dbo_assignments1.aposit = "Sr. Resident Engineer"



In the View option, Properties - Report - Data -
Record Source - Query is (following query defined)

SELECT [dbo_employees].[empno], [dbo_employees].[name], [dbo_approvals].[contractno], [dbo_assignments1].[aposit], [dbo_assignments1].[acontract], [dbo_assignments1].[astrdate], [dbo_assignments1].[aenddate]
FROM (dbo_employees INNER JOIN dbo_approvals ON [dbo_employees].[empno]=[dbo_approvals].[empno]) INNER JOIN dbo_assignments1 ON [dbo_employees].[empno]=[dbo_assignments1].[empno]
GROUP BY [dbo_approvals].[contractno], [dbo_employees].[empno], [dbo_employees].[name], [dbo_assignments1].[aposit], [dbo_assignments1].[acontract], [dbo_assignments1].[astrdate], [dbo_assignments1].[aenddate];


why it is given run time error ? I could not understand where I am making mistake.

Please give your e.mail address, so that I could send you one report attach file(in small access db, with three tables, in zip file)
Please you check which option I am doing mistake.
Or you send one simple report (small db three tables)
order by above case statment

Please help.

Mateen
[email protected]

do not prefix with dbo.tablename us only the field name or text box name. make sure that your field aposit is added to the report section and that the text box name is aposit.





Sal

I don't use prefix with dbo.tablename, I only use field name or
text box name. My field aposit is added to the report section and that the text box name is aposit
I use following code in the code builder

Select Case True
Case aposit = "Sr. Resident Engineer"
              txtvalue = 1
Case aposit = "Resident Engineer"
               txtvalue = 2
Case aposit = "Asst. Resident Engineer"
               txtvalue = 3
Case aposit = "Quantity Surveyor"
               txtvalue = 4
Case aposit = "Inspector"
               txtvalue = 5
Case aposit = "Inspector (As Built)"
               txtvalue = 6
Case aposit = "Surveyor"
               txtvalue = 7
Case aposit = "Technician"
               txtvalue = 8
Case aposit = "Secretary"
               txtvalue = 9
Case aposit = "Clerk / Expeditor"
               txtvalue = 10
Case aposit = "Driver"
               txtvalue = 11
Case aposit = "Party Chief"
               txtvalue = 12
End Select
End Sub


it is only displaying in the textbox 1, 2, 3, 4,.... respective value
in front of each aposit field.

Sr. Resident Engineer 1
Resident Engineer 2
....

it don't make ordering. as we want.

in each group it should be display order like following way.

contractno 221
--------------
Martin Sr. Resident Engineer
Peter Resident Engineer
Jones Asst. Resident Engineer
......
and so on......

contractno 2016
----------------
Wagner Sr. Resident Engineer
Tome Resident Engineer
Roome Asst. Resident Engineer
......
and so on......

regards.

Mateen

OR

Please you send any one example of above order by
query in the report. I will check (compared) with
my report.

regards

Mateen
[email protected]