Add commas to text string in VBA

W

Wayne-I-M

I need to add “ (one paid of commas) to the start and end of a string in VBA.


I need to create something like
"C:\Documents and Settings\My Documents\123.jpg"

what I am getting is
C:\Documents and Settings\My Documents\123.jpg



Eg. (this does not work)

inbox txtPath would be
C:\Documents and Settings\My Documents\

Private Sub ButtonName _Click()
If Dir(Me.txtPath &[ClientID] & ".jpg")<> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

But this does work

Private Sub ButtonName _Click()
If Dir("C:\Documents and Settings\My Documents\123.jpg") <> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

And adding the commas to the text box will not work as I need to add the
[ClientID] & ".jpg" after the path section to give the full path to the file.

The reason is that the 2nd code has the path including the proceeding “ and
the ending “.

How do I add the “ (commas) to the start and end of the path section of the
code.

I have tried
If Dir(“â€â€& Me.txtPath & [ClientID] & ".jpg"&â€â€â€)
And
If Dir(“â€â€& Me.txtPath & [ClientID] & ".jpgâ€â€)
Nether work

Any ideas would be really helpful.
 
B

BruceM

That character is knows as a quote mark, or maybe a double quote. Using the
Chr function (Help has more information on Chr), Chr(34) represents the
double quote.

Try something like this:

Dim strPath as string

strPath = Chr(34) & Me.txtPath & Me.ClientID & ".jpg" & Chr(34)

You could also do:
strPath = """" & Me.txtPath & Me.ClientID & ".jpg"""

There may be another option or two as well. In any case, substitute strPath
for:
Me.txtPath & [ClientID] & ".jpg":
If Dir(strPath) <> "" Then
etc.
 
C

Clif McIrvin

Wayne, what happens if you try

dim strFullPath as string
strFullPath = Me.txtPath &[ClientID] & ".jpg")<>
If Dir(strFullPath )<> "" Then

??

At least, you can set a breakpoint and see what is in strFullPath, which
you can't do as you coded it.

My guess is that you're running into trouble with the &[ClientID] & not
giving you what you expect.

--
Clif

Wayne-I-M said:
I need to add " (one paid of commas) to the start and end of a string
in VBA.


I need to create something like
"C:\Documents and Settings\My Documents\123.jpg"

what I am getting is
C:\Documents and Settings\My Documents\123.jpg



Eg. (this does not work)

inbox txtPath would be
C:\Documents and Settings\My Documents\

Private Sub ButtonName _Click()
If Dir(Me.txtPath &[ClientID] & ".jpg")<> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

But this does work

Private Sub ButtonName _Click()
If Dir("C:\Documents and Settings\My Documents\123.jpg") <> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

And adding the commas to the text box will not work as I need to add
the
[ClientID] & ".jpg" after the path section to give the full path to
the file.

The reason is that the 2nd code has the path including the proceeding
" and
the ending ".

How do I add the " (commas) to the start and end of the path section
of the
code.

I have tried
If Dir("""& Me.txtPath & [ClientID] & ".jpg"&""")
And
If Dir("""& Me.txtPath & [ClientID] & ".jpg"")
Nether work

Any ideas would be really helpful.
Click to expand...
 
W

Wayne-I-M

OK - combination of the 2 (cheers for those by the way)

what I have now is

Private Sub PhotoCheckerBut_Click()
Dim strPath As String
strPath = Chr(34) & Me.Parent.txtPhotoPath & Me.CDClientID & ".jpg" & Chr(34)
If Dir(strPath) <> "" Then
MsgBox "Photo is on file and is linked correctly", vbInformation, "Message
from Wayne"
Else
MsgBox "The photo file does not exist or is incorrectly numbered",
vbInformation, "Message from Wayne"
End If
End Sub

I am getting error 52
Bad File Name or Number

and this line is being highlighted
If Dir(strPath) <> "" Then

I have check the path in Me.Parent.txtPhotoPath
Note this is called from a subform and txtPhotoPath is on the main - so the
Me.Parent.

any ideas on error 52 ??

--
Wayne
Manchester, England.



Clif McIrvin said:
Wayne, what happens if you try

dim strFullPath as string
strFullPath = Me.txtPath &[ClientID] & ".jpg")<>
If Dir(strFullPath )<> "" Then

??

At least, you can set a breakpoint and see what is in strFullPath, which
you can't do as you coded it.

My guess is that you're running into trouble with the &[ClientID] & not
giving you what you expect.

--
Clif

Wayne-I-M said:
I need to add " (one paid of commas) to the start and end of a string
in VBA.


I need to create something like
"C:\Documents and Settings\My Documents\123.jpg"

what I am getting is
C:\Documents and Settings\My Documents\123.jpg



Eg. (this does not work)

inbox txtPath would be
C:\Documents and Settings\My Documents\

Private Sub ButtonName _Click()
If Dir(Me.txtPath &[ClientID] & ".jpg")<> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

But this does work

Private Sub ButtonName _Click()
If Dir("C:\Documents and Settings\My Documents\123.jpg") <> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

And adding the commas to the text box will not work as I need to add
the
[ClientID] & ".jpg" after the path section to give the full path to
the file.

The reason is that the 2nd code has the path including the proceeding
" and
the ending ".

How do I add the " (commas) to the start and end of the path section
of the
code.

I have tried
If Dir("""& Me.txtPath & [ClientID] & ".jpg"&""")
And
If Dir("""& Me.txtPath & [ClientID] & ".jpg"")
Nether work

Any ideas would be really helpful.
Click to expand...
Click to expand...
 
C

Clif McIrvin

what do you get if you add a

debug.print strPath (or msgbox strPath)

in front of the error line? If nothing else, copy and paste the contents
of strPath here...

--
Clif

Wayne-I-M said:
OK - combination of the 2 (cheers for those by the way)

what I have now is

Private Sub PhotoCheckerBut_Click()
Dim strPath As String
strPath = Chr(34) & Me.Parent.txtPhotoPath & Me.CDClientID & ".jpg" &
Chr(34)
If Dir(strPath) <> "" Then
MsgBox "Photo is on file and is linked correctly", vbInformation,
"Message
from Wayne"
Else
MsgBox "The photo file does not exist or is incorrectly numbered",
vbInformation, "Message from Wayne"
End If
End Sub

I am getting error 52
Bad File Name or Number

and this line is being highlighted
If Dir(strPath) <> "" Then

I have check the path in Me.Parent.txtPhotoPath
Note this is called from a subform and txtPhotoPath is on the main -
so the
Me.Parent.

any ideas on error 52 ??

--
Wayne
Manchester, England.



Clif McIrvin said:
Wayne, what happens if you try

dim strFullPath as string
strFullPath = Me.txtPath &[ClientID] & ".jpg")<>
If Dir(strFullPath )<> "" Then

??

At least, you can set a breakpoint and see what is in strFullPath,
which
you can't do as you coded it.

My guess is that you're running into trouble with the &[ClientID] &
not
giving you what you expect.

--
Clif

Wayne-I-M said:
I need to add " (one paid of commas) to the start and end of a
string
in VBA.


I need to create something like
"C:\Documents and Settings\My Documents\123.jpg"

what I am getting is
C:\Documents and Settings\My Documents\123.jpg



Eg. (this does not work)

inbox txtPath would be
C:\Documents and Settings\My Documents\

Private Sub ButtonName _Click()
If Dir(Me.txtPath &[ClientID] & ".jpg")<> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

But this does work

Private Sub ButtonName _Click()
If Dir("C:\Documents and Settings\My Documents\123.jpg") <> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

And adding the commas to the text box will not work as I need to
add
the
[ClientID] & ".jpg" after the path section to give the full path to
the file.

The reason is that the 2nd code has the path including the
proceeding
" and
the ending ".

How do I add the " (commas) to the start and end of the path
section
of the
code.

I have tried
If Dir("""& Me.txtPath & [ClientID] & ".jpg"&""")
And
If Dir("""& Me.txtPath & [ClientID] & ".jpg"")
Nether work

Any ideas would be really helpful.
Click to expand...
Click to expand...
Click to expand...
 
W

Wayne-I-M

I don't think this is an access problem

I just created a folder
C:\wayne\
can't get simpler than that - and then loaded (typed the full path in)
the ictures are not being recognised.

I will look at it in the morning

many thanks for you help

--
Wayne
Manchester, England.



Clif McIrvin said:
what do you get if you add a

debug.print strPath (or msgbox strPath)

in front of the error line? If nothing else, copy and paste the contents
of strPath here...

--
Clif

Wayne-I-M said:
OK - combination of the 2 (cheers for those by the way)

what I have now is

Private Sub PhotoCheckerBut_Click()
Dim strPath As String
strPath = Chr(34) & Me.Parent.txtPhotoPath & Me.CDClientID & ".jpg" &
Chr(34)
If Dir(strPath) <> "" Then
MsgBox "Photo is on file and is linked correctly", vbInformation,
"Message
from Wayne"
Else
MsgBox "The photo file does not exist or is incorrectly numbered",
vbInformation, "Message from Wayne"
End If
End Sub

I am getting error 52
Bad File Name or Number

and this line is being highlighted
If Dir(strPath) <> "" Then

I have check the path in Me.Parent.txtPhotoPath
Note this is called from a subform and txtPhotoPath is on the main -
so the
Me.Parent.

any ideas on error 52 ??

--
Wayne
Manchester, England.



Clif McIrvin said:
Wayne, what happens if you try

dim strFullPath as string
strFullPath = Me.txtPath &[ClientID] & ".jpg")<>
If Dir(strFullPath )<> "" Then

??

At least, you can set a breakpoint and see what is in strFullPath,
which
you can't do as you coded it.

My guess is that you're running into trouble with the &[ClientID] &
not
giving you what you expect.

--
Clif

I need to add " (one paid of commas) to the start and end of a
string
in VBA.


I need to create something like
"C:\Documents and Settings\My Documents\123.jpg"

what I am getting is
C:\Documents and Settings\My Documents\123.jpg



Eg. (this does not work)

inbox txtPath would be
C:\Documents and Settings\My Documents\

Private Sub ButtonName _Click()
If Dir(Me.txtPath &[ClientID] & ".jpg")<> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

But this does work

Private Sub ButtonName _Click()
If Dir("C:\Documents and Settings\My Documents\123.jpg") <> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

And adding the commas to the text box will not work as I need to
add
the
[ClientID] & ".jpg" after the path section to give the full path to
the file.

The reason is that the 2nd code has the path including the
proceeding
" and
the ending ".

How do I add the " (commas) to the start and end of the path
section
of the
code.

I have tried
If Dir("""& Me.txtPath & [ClientID] & ".jpg"&""")
And
If Dir("""& Me.txtPath & [ClientID] & ".jpg"")
Nether work

Any ideas would be really helpful.
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

Cliff's point was to look at the actual value of strPath by using
Debug.Print (view it by pressing Ctrl + G after running the code) or a
message box. We don't know what is in Me.Parent.txtPhotoPath or
Me.CDClientID. It could be something as simple as needing a backslash,
which may or may not be in txtPhotoPath or CDClientID.

Wayne-I-M said:
I don't think this is an access problem

I just created a folder
C:\wayne\
can't get simpler than that - and then loaded (typed the full path in)
the ictures are not being recognised.

I will look at it in the morning

many thanks for you help

--
Wayne
Manchester, England.



Clif McIrvin said:
what do you get if you add a

debug.print strPath (or msgbox strPath)

in front of the error line? If nothing else, copy and paste the contents
of strPath here...

--
Clif

Wayne-I-M said:
OK - combination of the 2 (cheers for those by the way)

what I have now is

Private Sub PhotoCheckerBut_Click()
Dim strPath As String
strPath = Chr(34) & Me.Parent.txtPhotoPath & Me.CDClientID & ".jpg" &
Chr(34)
If Dir(strPath) <> "" Then
MsgBox "Photo is on file and is linked correctly", vbInformation,
"Message
from Wayne"
Else
MsgBox "The photo file does not exist or is incorrectly numbered",
vbInformation, "Message from Wayne"
End If
End Sub

I am getting error 52
Bad File Name or Number

and this line is being highlighted
If Dir(strPath) <> "" Then

I have check the path in Me.Parent.txtPhotoPath
Note this is called from a subform and txtPhotoPath is on the main -
so the
Me.Parent.

any ideas on error 52 ??

--
Wayne
Manchester, England.



:

Wayne, what happens if you try

dim strFullPath as string
strFullPath = Me.txtPath &[ClientID] & ".jpg")<>
If Dir(strFullPath )<> "" Then

??

At least, you can set a breakpoint and see what is in strFullPath,
which
you can't do as you coded it.

My guess is that you're running into trouble with the &[ClientID] &
not
giving you what you expect.

--
Clif

I need to add " (one paid of commas) to the start and end of a
string
in VBA.


I need to create something like
"C:\Documents and Settings\My Documents\123.jpg"

what I am getting is
C:\Documents and Settings\My Documents\123.jpg



Eg. (this does not work)

inbox txtPath would be
C:\Documents and Settings\My Documents\

Private Sub ButtonName _Click()
If Dir(Me.txtPath &[ClientID] & ".jpg")<> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

But this does work

Private Sub ButtonName _Click()
If Dir("C:\Documents and Settings\My Documents\123.jpg") <> "" Then
MsgBox "Some possitive message", vbInformation, "Title"
Else
MsgBox "Some neggative message", vbInformation, "Title"
End If
End Sub

And adding the commas to the text box will not work as I need to
add
the
[ClientID] & ".jpg" after the path section to give the full path to
the file.

The reason is that the 2nd code has the path including the
proceeding
" and
the ending ".

How do I add the " (commas) to the start and end of the path
section
of the
code.

I have tried
If Dir("""& Me.txtPath & [ClientID] & ".jpg"&""")
And
If Dir("""& Me.txtPath & [ClientID] & ".jpg"")
Nether work

Any ideas would be really helpful.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Working outlook via vba code with xlsx - error 0
Add Date Modified to file list 6
Mad commas appearing in online DB 4
Automatically reattach SQL backend tables 1
Msg Box Title 5
Assigning a textbox string throughout code 1
Excel VBA - Force 4 commas into a text field on userform 7

Top