Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old April 11th, 2004, 04:08 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #12 (permalink)  
Old April 12th, 2004, 05:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
mateenmohd@hotmail.com




Quote:
quote:Originally posted by sal
 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
Reply With Quote
  #13 (permalink)  
Old April 15th, 2004, 01:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Quote:
quote:Originally posted by sal
 Straight from access help

IIf(IsNull([UnitPrice]), 0, [UnitPrice])
Changes a Null value to a zero (0) in the UnitPrice field

_____________________

You would have to nest it quite a bit.

But if you use this, you will be slowing down your processes by not using SQL Server engine.

Or you could send the values to the report without the case statement and put this code on the format event of the report.

select case true
CASE aposit = 'Sr. Resident Engineer'
                txtValue = 1
CASE aposit = ''Resident Engineer'
                txtvalue = 2
case . . . . .
END SELECT

that way you do not have to use Jet.



Sal
Reply With Quote
  #14 (permalink)  
Old April 16th, 2004, 09:22 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

enter another case line for any other values

case else
        txtvalue = "whatever"

or txtvalue = ""



Sal
Reply With Quote
  #15 (permalink)  
Old April 18th, 2004, 05:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Quote:
quote:Originally posted by sal
 enter another case line for any other values

case else
        txtvalue = "whatever"

or txtvalue = ""



Sal
Reply With Quote
  #16 (permalink)  
Old April 18th, 2004, 01:46 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #17 (permalink)  
Old April 20th, 2004, 01:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
mateenmohd@hotmail.com




Quote:
quote:Originally posted by sal
 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
Reply With Quote
  #18 (permalink)  
Old April 20th, 2004, 11:31 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #19 (permalink)  
Old April 22nd, 2004, 12:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Quote:
quote:Originally posted by sal
 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
Reply With Quote
  #20 (permalink)  
Old May 6th, 2004, 03:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OR

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

regards

Mateen
mateenmohd@hotmail.com


Quote:
quote:Originally posted by sal
 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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Case in Where Clause acko SQL Server 2000 12 September 12th, 2011 01:43 PM
CASE WHEN WHERE CLAUSE deontae45 SQL Server 2000 2 January 11th, 2011 10:03 AM
Problems using case statement in where clause vghiya SQL Server 2000 3 May 28th, 2007 05:12 AM
Case in Where Clause demiwolf SQL Server 2000 3 March 23rd, 2006 12:45 PM
case clause in query mateenmohd SQL Server 2000 2 February 22nd, 2005 05:49 AM



All times are GMT -4. The time now is 04:33 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.