|
 |
access thread: Suppress zeros from showing up in reports
Message #1 by "Richard Lyons" <rlyons@c...> on Thu, 24 Jan 2002 13:53:41 -0600
|
|
Is there a way to order these accordingly?
Regards,
Eric
----------------------------------------------------------------------
Subject: RE: Sorting text numbers in order
From: Joe Constant <jconstant@h...>
Date: Wed, 23 Jan 2002 08:01:14 -0800
X-Message-Number: 7
SELECT MyTextNumber
FROM MyTable
ORDER BY CLng([MyTextNumber]);
Joe
-----Original Message-----
From: simonds@m... [mailto:simonds@m...]
Sent: Wednesday, January 23, 2002 7:54 AM
To: Access
Subject: Sorting text numbers in order
I have a database that has a list of policies for a manual. Each section,
sub-section, and sub sub-section is numbered accrodingly, e.g., 80.013.003.
I want to order the sections numbers in sequential order, but what I get
is anything with a one comes first, then the twos, etc.
For example:
80.013.010
80.013.100
80.013.120
80.013.020
80.013.200
...
They should appear in proper order (i.e., 10, 20, 100, 120, 200...). I
know that the data type was originally a number, but was changed to text
because as a number it left out the leading zeros (three digit lengths are
needed). The table data has the three digit numbered stored, and each
section, sub-sedtion, and sub sub-section is stored in a seaprate field.
Is there a way to order these accordingly?
Regards,
Eric
----------------------------------------------------------------------
Subject: RE: Sorting text numbers in order
From: "David Adams" <davidadams00@h...>
Date: Wed, 23 Jan 2002 11:11:39 -0500
X-Message-Number: 8
I do not know if you can convert the text string 80.013.010 to a number.
You can always store the three parses of the value (80 13 10) in three
separate numeric fields and handle your select in the manner that Joe
suggested.
SELECT MyTextNumber
FROM MyTable
ORDER BY [ParseValue1],[ParseValue2],[ParseValue3]
Good luck.
David Adams
----- Original Message -----
From: "Joe Constant" <jconstant@h...>
To: "Access" <access@p...>
Sent: Wednesday, January 23, 2002 11:01 AM
Subject: [access] RE: Sorting text numbers in order
> SELECT MyTextNumber
> FROM MyTable
> ORDER BY CLng([MyTextNumber]);
>
>
> Joe
>
>
> -----Original Message-----
> From: simonds@m... [mailto:simonds@m...]
> Sent: Wednesday, January 23, 2002 7:54 AM
> To: Access
> Subject: Sorting text numbers in order
>
>
> I have a database that has a list of policies for a manual. Each section,
> sub-section, and sub sub-section is numbered accrodingly, e.g.,
80.013.003.
>
> I want to order the sections numbers in sequential order, but what I get
> is anything with a one comes first, then the twos, etc.
>
> For example:
> 80.013.010
> 80.013.100
> 80.013.120
> 80.013.020
> 80.013.200
> ...
>
> They should appear in proper order (i.e., 10, 20, 100, 120, 200...). I
> know that the data type was originally a number, but was changed to text
> because as a number it left out the leading zeros (three digit lengths are
> needed). The table data has the three digit numbered stored, and each
> section, sub-sedtion, and sub sub-section is stored in a seaprate field.
>
> Is there a way to order these accordingly?
>
> Regards,
> Eric
>
>
>
----------------------------------------------------------------------
Subject: Re: InStr problem
From: "Gregory Serrano" <serranog@m...>
Date: Wed, 23 Jan 2002 16:20:16
X-Message-Number: 9
Toni,
<< Can someone tell me why this statement fails in Access97 when the
string SID contains CTRLTH232099
pos = InStr(SID, "B", vbBinaryCompare)
I simply want to know if the string contains a capital B. I keep getting a
type mismatch error. >>
Did you declare the variable "pos" as a variant? The function INSTR
returns a variant. Try adding this before the statement:
Dim pos as Variant
Greg
----------------------------------------------------------------------
Subject: Re: Sorting text numbers in order
From: "Gregory Serrano" <serranog@m...>
Date: Wed, 23 Jan 2002 16:31:03
X-Message-Number: 10
Eric,
<< I have a database that has a list of policies for a manual. Each
section, sub-section, and sub sub-section is numbered accrodingly, e.g.,
80.013.003.
I want to order the sections numbers in sequential order, but what I get
is anything with a one comes first, then the twos, etc. >>
Don't store the policy number as the string "XXX.XXX.XXX". Instead store
them as three separate numerical fields called Section, Subsection and
Subsubsection. In your query, sort by Section first, then subsection,
then section. Since they're numbers, they'll sort accordingly. Then
create a field in your query (don't sort it) as such:
Policy: Format(Section, "000") & "." & Format(Subsection, "000") & "." &
Format(Subsubsection, "000")
The policy number is never stored anywhere. It's always reconstructed
when needed from its three parts, which are stored as numbers.
Greg
----------------------------------------------------------------------
Subject: Corel WordPerfect 9 and Access 97/2000
From: "Vernon Vincent" <vvincent@a...>
Date: Wed, 23 Jan 2002 17:09:57
X-Message-Number: 11
I'm trying to instantiate a new instance of WordPerfect from within
access, but I keep getting Runtime Error 429: ActiveX Component can't
create object.
The code I have at the moment is this:
Dim objWP As WordPerfect.Application
Dim doc As WordPerfect.Document
Set objWP = New WordPerfect.Application
The error keeps occurring at the Set command.
Since this is the farthest I have gotten, I don't have anything else
written, but the goal is to have Access create an instance of WordPerfect
with a specific template and the populate specific bookmarks with data
from the Access database.
Any thoughts would be greatly appreciated.
Vernon L. Vincent
----------------------------------------------------------------------
Subject: RE: Corel WordPerfect 9 and Access 97/2000
From: "Leo Scott" <leoscott@c...>
Date: Wed, 23 Jan 2002 10:28:44 -0800
X-Message-Number: 12
Do you have a reference set to a Wordperfect object library in
Tools->References. I know if I want to drive Excel from access I have to
set a reference to the Excel library.
>-----Original Message-----
>From: Vernon Vincent [mailto:vvincent@a...]
>Sent: Wednesday, January 23, 2002 5:10 PM
>To: Access
>Subject: [access] Corel WordPerfect 9 and Access 97/2000
>
>
>I'm trying to instantiate a new instance of WordPerfect from within
>access, but I keep getting Runtime Error 429: ActiveX Component can't
>create object.
>
>The code I have at the moment is this:
>
> Dim objWP As WordPerfect.Application
> Dim doc As WordPerfect.Document
>
> Set objWP = New WordPerfect.Application
>
>The error keeps occurring at the Set command.
>
>Since this is the farthest I have gotten, I don't have anything else
>written, but the goal is to have Access create an instance of WordPerfect
>with a specific template and the populate specific bookmarks with data
>from the Access database.
>
>Any thoughts would be greatly appreciated.
>
>Vernon L. Vincent
>
>
----------------------------------------------------------------------
Subject: RE: Corel WordPerfect 9 and Access 97/2000
From: "Rogers, Robert" <rrogers@b...>
Date: Wed, 23 Jan 2002 14:11:37 -0500
X-Message-Number: 13
I'm use this public function to connect to my SQL Server but it's very slow.
Any suggest on how to rewrite this code to speed the connect up.
Public Function ODBCConnect()
ODBCConnect = "ODBC;DRIVER=SQL Server;SERVER=branchsql0000;UID=" &
Forms![LoginForm]![cuser] & ";PWD=" & Forms![LoginForm]![cpwd] &
";DATABASE=Ori0on02k00000"
End Function
Robert L. Rogers
Rexel Branch Electric
Application Developer
xxx-xxx-xxxx
****************************************************************************
******************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this e-mail in error please notify the sender and
delete/destroy any copies.
****************************************************************************
******************************************************
----------------------------------------------------------------------
Subject: Re: ADO vs DAO Performance
From: "Paul McLaren" <paulmcl@t...>
Date: Wed, 23 Jan 2002 19:16:08 -0000
X-Message-Number: 14
Thanks to all who contributed on this one, strange findings from what I
read.
Thanks
Paul
-----Original Message-----
From: Sameer Peera [mailto:dreamreapr@a...]
Sent: 23 January 2002 03:23
To: Access
Subject: [access] Re: ADO vs DAO Performance
Hi Paul,
From a book on Access Programming:
"Using ADO with a Jet Database can result in five to ten times slower
performance than using DAO. When you query a database using ADO, it must
retrieve information about the database before it can communicate with
it.
ADO treats all databases as external databases. So, even though you are
building an Access application against an Access database, ADO still has
to retreive this information. Since DAO was optimized for accessing Jet
databases, it already has most of the information it needs abou tthe
database."
Hope this helps..
-Sameer
> Anyone done any tests or have any info on which is faster - ADO or
> DAO.
>
> The scenario would be a frontend user database with forms etc and
backend
> database holding the tables.
>
> Both the frontend and backend would be in Access.
>
> I have done some initial tests creating a loop that opens the
connection,
> writes a new record and closes the connection, this is repeated in 100
> times.
>
> ADO is coming out 3 times slower than DAO.
>
> Any views are appreciated (or am I missing the point!)
>
> Thanks
>
> Paul
----------------------------------------------------------------------
Subject: exporting report in RTF
From: "Lesley Schulz" <lesley.schulz@h...>
Date: Wed, 23 Jan 2002 19:43:04
X-Message-Number: 15
I have a report that I have created in Access 97 and when I export it into
RTF (rich text format) it puts multiple part numbers instead of one. It
repeats the part number 2-3 times. When I view it in access there is only
one part number.
Any help for that?
Lesley
----------------------------------------------------------------------
Subject: Re: InStr problem
From: "Toni Steinhauer" <toni@t...>
Date: Wed, 23 Jan 2002 19:49:51
X-Message-Number: 16
> Toni,
>
> << Can someone tell me why this statement fails in Access97 when the
> string SID contains CTRLTH232099
>
> pos = InStr(SID, "B", vbBinaryCompare)
>
> I simply want to know if the string contains a capital B. I keep
getting a
> type mismatch error. >>
>
> Did you declare the variable "pos" as a variant? The function INSTR
> returns a variant. Try adding this before the statement:
>
> Dim pos as Variant
>
> Greg
Greg:
I tried that with no luck. Still got the type mismatch.
Here is the code that illustrates my real problem:
SID = "CTRL90b90e9s"
pos = InStr(SID, "B") 'look for B
If pos > 0 Then
pos = InStr(pos, SID, "E") 'look for E
If pos > 0 Then
pos = InStr(pos, SID, "S") 'look for S
If pos > 0 Then 'have a BES format
MsgBox "SID contains BES"
End If
End If
End If
The message box is supposed to show ONLY if the letters BES in upper case
appear in that order in the string. I had added the vbBinaryCompare to
try to force it to only accept upper case. In the example above, I get
the message box to appear even though the letters BES appear in lower
case in the string. Any ideas?
----------------------------------------------------------------------
Subject: RE: InStr problem
From: "Ian Ashton" <ian@c...>
Date: Wed, 23 Jan 2002 20:08:28 -0000
X-Message-Number: 17
Toni,
If you specify the optional variable [Compare], you also need to specify the
optional variable [Start]
The following works.
lngPos = InStr(1, strSID, "B", vbBinaryCompare)
(InStr returns a Variant of type Long. I other words lngPos should be
dimensioned as a long).
Ian Ashton
-----Original Message-----
From: Toni Steinhauer [mailto:toni@t...]
Sent: Wednesday, January 23, 2002 2:38 PM
To: Access
Subject: [access] InStr problem
Can someone tell me why this statement fails in Access97 when the string
SID contains CTRLTH232099
pos = InStr(SID, "B", vbBinaryCompare)
I simply want to know if the string contains a capital B. I keep getting
a type mismatch error.
Thanks!
----------------------------------------------------------------------
Subject: RE: InStr problem
From: "Toni Steinhauer" <toni@t...>
Date: Wed, 23 Jan 2002 21:50:38
X-Message-Number: 18
Ian:
That was the problem! You are a life saver! I have been wrestling with
this all day!
The second and third test (for E and S) would have been fine, since I did
specify a starting point. I just omitted the starting point on the test
for 'B'.
Thanks again!
Toni
>
> Toni,
>
> If you specify the optional variable [Compare], you also need to
specify the
> optional variable [Start]
>
> The following works.
>
> lngPos = InStr(1, strSID, "B", vbBinaryCompare)
>
> (InStr returns a Variant of type Long. I other words lngPos should be
> dimensioned as a long).
>
> Ian Ashton
>
>
>
> -----Original Message-----
> From: Toni Steinhauer [mailto:toni@t...]
> Sent: Wednesday, January 23, 2002 2:38 PM
> To: Access
> Subject: [access] InStr problem
>
>
> Can someone tell me why this statement fails in Access97 when the string
> SID contains CTRLTH232099
>
> pos = InStr(SID, "B", vbBinaryCompare)
>
> I simply want to know if the string contains a capital B. I keep getting
> a type mismatch error.
>
> Thanks!
>
>
>
----------------------------------------------------------------------
Subject: Wildcard search in Access through a text box
From: "peter pennells" <ppenn@b...>
Date: Wed, 23 Jan 2002 21:54:13
X-Message-Number: 19
Can anybody help me with the following please
I want to have a form with a text box that will receive a wildcard search
and then have the result displayed as a datasheet, either as a sub form or
as another form. I have visited various Access help sites and viewed other
questions on this site but have yet to successfully create what I want.
I think the problem I am having is inputing the data from the text box
into the query correctly.
Many thanks in advance
Peter
----------------------------------------------------------------------
Subject: RE: Wildcard search in Access through a text box
From: "Yehuda Rosenblum" <Yehuda@I...>
Date: Wed, 23 Jan 2002 16:57:27 -0500
X-Message-Number: 20
Peter,
When we need to do a wildcard search we query based using a LIKE clause
on the specified data without the wildcard character. If I run into a
problem I design a sample query in Access and then look at the SQL view
for what I am doing wrong.
I hope this helps,
Yehuda
-----Original Message-----
From: peter pennells [mailto:ppenn@b...]
Sent: Wednesday, January 23, 2002 4:54 PM
To: Access
Subject: [access] Wildcard search in Access through a text box
Can anybody help me with the following please
I want to have a form with a text box that will receive a wildcard
search
and then have the result displayed as a datasheet, either as a sub form
or
as another form. I have visited various Access help sites and viewed
other
questions on this site but have yet to successfully create what I want.
I think the problem I am having is inputing the data from the text box
into the query correctly.
Many thanks in advance
Peter
----------------------------------------------------------------------
Subject: RE: Wildcard search in Access through a text box
From: jsaam@m...
Date: Wed, 23 Jan 2002 13:03:26 -0900
X-Message-Number: 21
Why not create a Query, add the tables/fields you want to show up in the
datasheet view, and for the Criteria, put in: Like "*" &[Criteria to search
for] & "*" under the Field that you want the query to ask.
Note that you can change the text in the brackets ([...]) to read what ever
you want the dialog box to ask the user or yourself. When you run the
query, a dialog box will appear..
Better yet, if you already have a form with the text box on it, you may be
able to take that query, and put in: LIKE "*" & Forms![Your_form_name].[Text
box name].Text & "*" <-- not sure on the syntax there.
Have fun..
-----Original Message-----
From: peter pennells [mailto:ppenn@b...]
Sent: Wednesday, January 23, 2002 12:54 PM
To: Access
Subject: [access] Wildcard search in Access through a text box
Can anybody help me with the following please
I want to have a form with a text box that will receive a wildcard search
and then have the result displayed as a datasheet, either as a sub form or
as another form. I have visited various Access help sites and viewed other
questions on this site but have yet to successfully create what I want. I
think the problem I am having is inputing the data from the text box
into the query correctly.
Many thanks in advance
Peter
----------------------------------------------------------------------
Subject: Enter Parameter Prompt
From: "Adeyemi Ajao" <adeyemiajao@h...>
Date: Wed, 23 Jan 2002 22:22:09 +0000
X-Message-Number: 22
Dear sirs,
I get prompted for a parameter when i do not expect it in running reports.
I used the function =Format(Date(),"mmm")to get the month part of system
date in text format.
The application work fine on the system I used originally, but when copied
to other systems the prompt to enter parameter for Format and Date is always
displayed.
I need assistance please !!!.
Sincerely,
Yemi Ajao
_________________________________________________________________
Join the world?s largest e-mail service with MSN Hotmail.
http://www.hotmail.com
----------------------------------------------------------------------
Subject: RE: Enter Parameter Prompt
From: "A. van Huizen" <ajvhzn@p...>
Date: Wed, 23 Jan 2002 23:37:13 +0100
X-Message-Number: 23
I've had this too.
Check your references in the VB editor (Tools -> References) and check
for Missing one's.
MSCAL.OCX is a famous one!
Regards,
Arjan
-----Original Message-----
From: Adeyemi Ajao [mailto:adeyemiajao@h...]
Sent: woensdag 23 januari 2002 23:22
To: Access
Subject: [access] Enter Parameter Prompt
Dear sirs,
I get prompted for a parameter when i do not expect it in running
reports.
I used the function =Format(Date(),"mmm")to get the month part of system
How can I suppress zeros in a report?
Message #2 by "John Ruff" <papparuff@c...> on Thu, 24 Jan 2002 12:37:32 -0800
|
|
I created a report with a field called txtSection. This is where the
Manual's section,sub-section, and sub-sub-section will be placed after
stripping the zeros from the field. In the report Detail On Print event
I placed the following code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
txtSection = ReplaceCharacter(Man_Section)
End Sub
I added a module and added the following function:
Public Function ReplaceCharacter(strString As String) As String
' Note: This only works if the format for strString
' XX.XXX.XXX
Dim strReplace As String
strReplace = ""
' Test for characters 1 thru 2
' If the first character of strString
' is a zero,
' set strReplace to equal the next
' two characters of strString, otherwise
' set strReplace to equal the first three
' characters in strString
If Left(strString, 1) = "0" Then
strReplace = Mid(strString, 2, 2)
Else
strReplace = Left(strString, 3)
End If
' Test for characters 4 thru 6
' If the first character of the second group of
' characters in strString does not equal 0 then
' concatenante the four characters after the \
' first dot to strReplace
If Mid(strString, 4, 1) <> "0" Then
strReplace = strReplace & Mid(strString, 4, 4)
' If the first two characters after the first dot
' are zeros, then concatenate only the last
' character and the next dot to strReplace
ElseIf Mid(strString, 4, 2) = "00" Then
strReplace = strReplace & Mid(strString, 6, 2)
' If only the first character after the first dot
' is a zero, then concatenate only the last
' two character and the next dot to strReplace
ElseIf Mid(strString, 4, 1) = "0" Then
strReplace = strReplace & Mid(strString, 5, 3)
End If
' Test for characters 8 thru 10
' If the first character after the second dot is
' not zero, concatenate the last three characters
' to strReplace
If Mid(strString, 8, 1) <> "0" Then
strReplace = strReplace & Right(strString, 3)
' If the first two characters after the first dot
' are zeros, concatenate only the last character
' to strReplace
ElseIf Mid(strString, 8, 2) = "00" Then
strReplace = strReplace & Right(strString, 1)
' If only the first character after the second dot
' is a zero, concatenate the last two characters
' to strReplace
ElseIf Mid(strString, 8, 1) = "0" Then
strReplace = strReplace & Right(strString, 2)
End If
ReplaceCharacter = strReplace
End Function
What the code will do is"
If your original string is 01.001.001, it will be changed to 1.1.1 on
the report
If your original string is 80.020.200, it will be changed to 80.20.200
on the report
If your original string is 80.200.010, it will be changed to 80.200.10
on the report
Is this what you were looking for?
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Richard Lyons [mailto:rlyons@c...]
Sent: Thursday, January 24, 2002 11:54 AM
To: Access
Subject: [access] Suppress zeros from showing up in reports
Is there a way to order these accordingly?
Regards,
Eric
----------------------------------------------------------------------
Message #3 by "John Ruff" <papparuff@c...> on Thu, 24 Jan 2002 12:39:36 -0800
|
|
I forgot to tell you that I caled the field in the table that holds the
Manual's section, sub-section, and sub-sub-section Man_Section
-----Original Message-----
From: John Ruff [mailto:papparuff@c...]
Sent: Thursday, January 24, 2002 12:38 PM
To: Access
Subject: [access] RE: Suppress zeros from showing up in reports
I created a report with a field called txtSection. This is where the
Manual's section,sub-section, and sub-sub-section will be placed after
stripping the zeros from the field. In the report Detail On Print event
I placed the following code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
txtSection = ReplaceCharacter(Man_Section)
End Sub
I added a module and added the following function:
Public Function ReplaceCharacter(strString As String) As String ' Note:
This only works if the format for strString
' XX.XXX.XXX
Dim strReplace As String
strReplace = ""
' Test for characters 1 thru 2
' If the first character of strString
' is a zero,
' set strReplace to equal the next
' two characters of strString, otherwise
' set strReplace to equal the first three
' characters in strString
If Left(strString, 1) = "0" Then
strReplace = Mid(strString, 2, 2)
Else
strReplace = Left(strString, 3)
End If
' Test for characters 4 thru 6
' If the first character of the second group of
' characters in strString does not equal 0 then
' concatenante the four characters after the \
' first dot to strReplace
If Mid(strString, 4, 1) <> "0" Then
strReplace = strReplace & Mid(strString, 4, 4)
' If the first two characters after the first dot
' are zeros, then concatenate only the last
' character and the next dot to strReplace
ElseIf Mid(strString, 4, 2) = "00" Then
strReplace = strReplace & Mid(strString, 6, 2)
' If only the first character after the first dot
' is a zero, then concatenate only the last
' two character and the next dot to strReplace
ElseIf Mid(strString, 4, 1) = "0" Then
strReplace = strReplace & Mid(strString, 5, 3)
End If
' Test for characters 8 thru 10
' If the first character after the second dot is
' not zero, concatenate the last three characters
' to strReplace
If Mid(strString, 8, 1) <> "0" Then
strReplace = strReplace & Right(strString, 3)
' If the first two characters after the first dot
' are zeros, concatenate only the last character
' to strReplace
ElseIf Mid(strString, 8, 2) = "00" Then
strReplace = strReplace & Right(strString, 1)
' If only the first character after the second dot
' is a zero, concatenate the last two characters
' to strReplace
ElseIf Mid(strString, 8, 1) = "0" Then
strReplace = strReplace & Right(strString, 2)
End If
ReplaceCharacter = strReplace
End Function
What the code will do is"
If your original string is 01.001.001, it will be changed to 1.1.1 on
the report If your original string is 80.020.200, it will be changed to
80.20.200 on the report If your original string is 80.200.010, it will
be changed to 80.200.10 on the report
Is this what you were looking for?
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Richard Lyons [mailto:rlyons@c...]
Sent: Thursday, January 24, 2002 11:54 AM
To: Access
Subject: [access] Suppress zeros from showing up in reports
Is there a way to order these accordingly?
Regards,
Eric
----------------------------------------------------------------------
|
|
|
View Cart
