We help IT Professionals succeed at work.

Access 2007, Truncate a number

752 Views
Last Modified: 2012-02-15
Thank you all!

I have this code:
Set rsSCGTable = New ADODB.Recordset
sSQLOLDSCGTable = "Select * from SCGOldNumTable where SCGID = '" & CboSCG & "'"
rsOLDSCGTable.Open sSQLOLDSCGTable, CurrentProject.Connection
If Not rsOLDSCGTable.EOF Then
'Check if unclassified
    If rsOLDSCGTable!Class <> "U" Then
        MsgBox "This is not a Unclassified Document"
        GoTo BeforeExit
    Else

        Path1 = CurrentProject.Path
        Path2 = "\Approved\"
        StrPath = Path1 & Path2
        ' Open the Matching PDF
        Application.FollowHyperlink StrPath & Me.CboSCG.Column(0) & ".pdf"
    End If
End If



I need the number to truncate if it does not find the cboSCG number
Example:
cboSCG number is: 01-0001.00
But the PDF number is: 01-0001.01

Truncate to: 01-0001 and come up with message that:
"Found another referance number do you want to open it" Y/N
If "Y" then
open PDF
end If

Thanks
Comment
Watch Question

CERTIFIED EXPERT

Commented:
To clarify my understanding...

You are saying .. if you can't find 01-0001.00 then look for any value with prefix 01-0001

What if there are several with that prefix?
Alfredo Luis Torres SerranoASP .Net Developer

Commented:
You need look for

Select * from SCGOldNumTable where SCGID = '" & 01-0001.00

if not found

Select * from SCGOldNumTable where SCGID = '" & 01-0001.01

if not found

Select * from SCGOldNumTable where SCGID = '" & 01-0001.02
.
.
.
.
.
Select * from SCGOldNumTable where SCGID = '" & 01-0001.99

if any found

Select * from SCGOldNumTable where SCGID = '" & 01-0001

??????
Alfredo Luis Torres SerranoASP .Net Developer

Commented:
Are you doing all this on Access VBA or a VB form???

How is you development environment???

Author

Commented:
Thanks,

The Comment:
To clarify my understanding...

You are saying .. if you can't find 01-0001.00 then look for any value with prefix 01-0001

A.)
This would be correct.

Q.)
What if there are several with that prefix?

If yes:
take the lastest version/revision

The:
.00 is a revision number.


Q.)
Are you doing all this on Access VBA or a VB form???

A.)
Access VB6.5 ???


Thanks

Author

Commented:
Thanks all,


Comment:
To clarify my understanding...

You are saying .. if you can't find 01-0001.00 then look for any value with prefix 01-0001



A.)
01-0001.00
is just an example.
the user can type any number into the textbox(cboSCG)

so this would not work at all:

Select * from SCGOldNumTable where SCGID = '" & 01-0001.02
.
.
.
.
.
Select * from SCGOldNumTable where SCGID = '" & 01-0001.99




Thank you!
CERTIFIED EXPERT
Top Expert 2009

Commented:
Are you looking at the whole string up to the decimal point?  In that case, this expression will yield it:

Mid(strCode, 1, InStr(strCode, ".") - 1)

Open in new window

Author

Commented:
Thanks,

so that would be:

strTrcId = Mid(cboSCG, 1, InStr(cboSCG, ".") - 1)
sSQL = "Select * from SCGOldNumTable where SCGID = '" & strTrcID & "'"


so would this code be correct?

Thanks
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
I might take this approach.  Use a Union query to get either the exact string or one beginning with the user's input string.

"Select * from SCGOldNumTable where SCGID = '" & txtUserstring & "' " & _
"Union ALL " & _
"Select * from SCGOldNumTable where SCGID Like '" & txtUserstring & "*'"

Open in new window

Author

Commented:
Thanks,

The problem is not in the string.

It is when I open the pdf?
Application.FollowHyperlink StrPath & Me.CboSCG.Column(0) & ".pdf"

Example:
This is the whole number:
01-0001.01
So lets seperate the number
This is the Main Number:
01-0001
This is the revision number:
.01

Now lets say that the whole number does not match, but the Main number matches.
Example:
User selects:
01-0001.01
from the drop-down combo box on the form

In the Folder there is a pdf titled:
01-0001.98

strTrcID = Main Number
strRevID = Revision Number

I found how to seperate the main number from the whole number:
strTrcID = Mid(CboSCG, 1, InStr(CboSCG, ".") - 1)

I need help with getting the revision number (.01) to start at .99 until it finds the correct revision number?
Maybe something like, but I know this is wrong I also need to put strRevID into an array HELP:
strRevID = Left(strCode, 1, InStr(strCode, ".") - 1)
If Err.Number = 490 And strRevID <> (.00)-1 Then
strTrcID = strTrcID + strRevID
        Application.FollowHyperlink StrPath & strTrcID & ".pdf"

Thanks
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
is the file name stored in the database?

Author

Commented:
thanks,

Q.)
is the file name stored in the database?

A.)
If you mean is the pdf title stored in the database?  Then some are and some just match the first 5 numbers to the title of the pdf.

Example:
Table:
02-002.02

Folder:
02-002.98

No match to the whole number.  Would have to truncate the number into 2 parts?  And  search desending starting from .99 when found then a message: "No exact match found, do you want to open the file?" [Y/N]

Please help and thanks
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That was it, thanks!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.