Simple Date problem

[Ryan Clair]

I have txtA and txtB.

Upon load, both are = Date

A is hidden, B is shown to the user.

If user changes B date to a different date, I want a hidden check box
to be marked as -1

Works okay unless user inputs the same date, then the comparison
operator that I'm using <> doesn't match the two. One date has quotes
around it, the other doesn't. Is there a better operator to use or is
there a way to concatenate quotes around the date?

Thanks in advance.

Ryan

 

[Bob Hairgrove]

I have txtA and txtB.

Upon load, both are = Date

A is hidden, B is shown to the user.

If user changes B date to a different date, I want a hidden check box
to be marked as -1

Works okay unless user inputs the same date, then the comparison
operator that I'm using <> doesn't match the two. One date has quotes
around it, the other doesn't. Is there a better operator to use or is
there a way to concatenate quotes around the date?

Thanks in advance.

Ryan

You can use the AfterUpdate event of the control to put the quotes in
via VBA code running in the event procedure. But parsing dates from
string input is a major PITA because of all the different possible
formats. What you really need is either to force the user to pick the
date out of a list, combo box or date/time ActiveX control, or else
provide separate text boxes or controls for year, month and date upon
which sanity checking can be performed.

 

[Ryan Clair]

I'm not too worried about format, just getting the date compared. What
would be the proper way to add quotes around it? I've tried

variable = " & variable2.value & "

and a number of combinations of this. What am I doing wrong?

 

[Bob Hairgrove]

I'm not too worried about format, just getting the date compared. What
would be the proper way to add quotes around it? I've tried

variable = " & variable2.value & "

and a number of combinations of this. What am I doing wrong?

Try this:

variable = """" & variable2.value & """"

You need FOUR double quotes in order to express the double quote
character in VBA as a literal. Using two single double quote
characters, the code parser will simply try to create a string out of
all the text between them.

Alternatively, you could use the Chr() function with the appropriate
ASCII value for the double quote ... I believe it is 34, but I'm too
lazy to look it up right now.

 

[Ryan Clair]

I had tried 3 before... but 4?

Well I tried it and it still makes the variable = """"" &
variable2.value & """"" (Yeah, that's right, 5 quotes... what the
heck?)

Very annoying. I had copied and pasted your code just to be sure
(twice).

Thanks for your help by the way.

Ryan

 

[Bob Hairgrove]

I had tried 3 before... but 4?

Well I tried it and it still makes the variable = """"" &
variable2.value & """"" (Yeah, that's right, 5 quotes... what the
heck?)

Very annoying. I had copied and pasted your code just to be sure
(twice).

Thanks for your help by the way.

Ryan

I'm at a loss as to why your VBA editor makes 5 quotes out of 4 ...
did you try just typing them in?

If you are building an SQL string in VBA code with this, you might be
better off using single quotes (i.e. the character ['] as opposed to
["]).

Maybe if you quoted some more context, we could help more/better...

 

[Ryan Clair]

Heh, I know. Very odd. So here is exactly what I have:

0 Private Sub txtDate_AfterUpdate()
1 txtHiddenDate = """" & txtHiddenDate.Value & """"
2
3 If txtDate.Value <> txtHiddenDate Then
4 chkFlag = -1
5 Else
6 chkFlag = 0
7 End If
8
9 End Sub

When I debug and step-into at line 3 and hover over txtHiddenDate, it
says:

txtHiddenDate = """ & txtHiddenDate.Value & """

When I hover over txtdate it says:

txtDate = "11/10/2006"

Note: It does have the quotes around the date unlike if I just forgone
line 1.

Ryan

I had tried 3 before... but 4?

Well I tried it and it still makes the variable = """"" &
variable2.value & """"" (Yeah, that's right, 5 quotes... what the
heck?)

Very annoying. I had copied and pasted your code just to be sure
(twice).

Thanks for your help by the way.

RyanI'm at a loss as to why your VBA editor makes 5 quotes out of 4 ...

did you try just typing them in?

If you are building an SQL string in VBA code with this, you might be
better off using single quotes (i.e. the character ['] as opposed to
["]).

Maybe if you quoted some more context, we could help more/better...

 

[Ryan Clair]

AHHH, I found whats wrong. I told you variable and didn't specify I was
just using the object's as the holder (i.e. text box). So I got to
thinking and I tried using variables like I claimed before. This works:

Private Sub txtDate_AfterUpdate()
Dim textbox As String
Dim modtxtbox As String

textbox = txtHiddenDate.Value

modtxtbox = "" & textbox & ""

If txtDate <> modtxtbox Then
chkFlag = -1
Else
chkFlag = 0
End If

End Sub

The four quotes didn't work, FYI.

Thanks for all your help!

Ryan

I had tried 3 before... but 4?

Well I tried it and it still makes the variable = """"" &
variable2.value & """"" (Yeah, that's right, 5 quotes... what the
heck?)

Very annoying. I had copied and pasted your code just to be sure
(twice).

Thanks for your help by the way.

RyanI'm at a loss as to why your VBA editor makes 5 quotes out of 4 ...

did you try just typing them in?

If you are building an SQL string in VBA code with this, you might be
better off using single quotes (i.e. the character ['] as opposed to
["]).

Maybe if you quoted some more context, we could help more/better...