PDA

View Full Version : VBA concenate problem



CuriousGeorg
09-09-2013, 02:52 AM
I have 2 cbo boxes one with a date one with a time. Im using VBA to combine those into a cell.

The CBO on their own use the UK date. However when they join together they change to the US date.

Code im using to join them is

.Offset(RowCount, 5).Value = DateValue(Me.fnoldate.Value) & " " & TimeValue(Me.fnoltime.Value)


IS there a way I can prevent this?

Aflatoon
09-09-2013, 03:27 AM
You're creating a string and passing that to the cell, which is why it gets interpreted as a US date (VBA is US-centric). Try using:
.Offset(RowCount, 5).Value = CDate(Me.fnoldate.Value & " " & Me.fnoltime.Value)

CuriousGeorg
09-09-2013, 04:03 AM
forgot about CDate. Putting that exact code in gave me a mismatch error. SO I put a bracket around like below and now Me.Fnoltime.Value) pulls a random date and the time put in the user form

.Offset(RowCount, 5).Value = CDate(Me.fnoldate.Value) & " " & (Me.fnoltime.Value)


Private Sub cmdOK_Click()

Dim RowCount As Long
Dim ctl As Control


If Me.txtClaimnumber.Value = "" Then
MsgBox "Please enter a Claim Reference.", vbExclamation, "Claim Reference Missing"
Me.txtClaimnumber.SetFocus
Exit Sub
End If
If Me.cboRTM.Value = "" Then
MsgBox "Please enter a Route to Market.", vbExclamation, "RTM Missing"
Me.cboRTM.SetFocus
Exit Sub
End If
If Me.cboassfrom.Value = "" Then
MsgBox "Please choose where Claim was Assigned From.", vbExclamation, "Assigned From Missing"
Me.cboassfrom.SetFocus
Exit Sub
End If
If Me.cboassto.Value = "" Then
MsgBox "Please choose where Claim is Assigned To.", vbExclamation, "Assigned To Missing"
Exit Sub
End If
RowCount = Cells(Rows.Count, 1).End(xlUp).Row - 6
With Worksheets("Amanda").Range("A7")
.Offset(RowCount, 0).Value = "Name"
.Offset(RowCount, 1).Value = Me.txtClaimnumber.Value
.Offset(RowCount, 2).Value = Me.cboRTM.Value
.Offset(RowCount, 3).Value = Me.cboassfrom.Value
.Offset(RowCount, 4).Value = CDate(Me.incidentdate.Value)
.Offset(RowCount, 5).Value = CDate(Me.fnoldate.Value) & " " & (Me.fnoltime.Value)
.Offset(RowCount, 6).Value = cDate(Me.pickuptime.Value) & " " & (Me.pickupdate.Value)
.Offset(RowCount, 7).Value = Me.cboLiabfnol.Value
.Offset(RowCount, 8).Value = Me.cboTelFnol.Value
.Offset(RowCount, 9).Value = Me.cboAddressfnol.Value
.Offset(RowCount, 10).Value = Me.cboLiabtriage.Value
.Offset(RowCount, 11).Value = Me.cboaddresstriage.Value
.Offset(RowCount, 12).Value = Me.cboNumbertriage.Value
.Offset(RowCount, 13).Value = Me.cboassto.Value
.Offset(RowCount, 14).Value = CDate(Me.handoffdate.Value) & " " & (Me.handofftime.Value)



End With


For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
ThisWorkbook.Save
End Sub

is the code im using for this ..
This results in rowcount 4 showing - 02/01/2013 (user entered 2nd January so this is correct)
rowcount 5 showing: 02/01/2013 28/08/2013 ! -- why 2 dates?
rowcount 6 shows : 01/02/2013 00:00 -

snb
09-09-2013, 04:15 AM
Since you didn't specify the contents of fnoldate nor of fnoltime we can only guess.
VBA never takes a random date unless you order it to do so.
An unexpected result doesn't equal a random result.


cells(1).Value = CDate(1*fnoldate + 1*fnoltime)

Aflatoon
09-09-2013, 04:15 AM
What exactly is in the two controls? If CDate gave you a type mismatch, then whatever it is is not recognisable as a date/time given your regional settings.

CuriousGeorg
09-09-2013, 04:26 AM
apologies. mind wandering.. fnoldate is a DTPicker. with format as 1 - dtpshortdate, fnoltime is also dtPicker using 2-dtpTime

Putting the date alone works fine.. aka .Offset(RowCount, 4).Value = CDate(Me.incidentdate.Value)

its when I try combine the date and the time into one cell.

snb
09-09-2013, 04:28 AM
Just add the 2 values.

CuriousGeorg
09-09-2013, 04:32 AM
the attached may help.. :bug: (attachment removed now problem solved)


i apologise if my explanations have been missing vital information to give a definitive answer.

Ive just been trying to work this out for a while trying different things.

CuriousGeorg
09-09-2013, 04:38 AM
And blindly I have solved it...

I apologise for your time.... categorized appears to have had a date mask hiding in it.

Aflatoon
09-09-2013, 04:40 AM
Try:
.Offset(RowCount, 5).Value = fnoldate + TimeValue(fnoltime)
.Offset(RowCount, 6).Value = TimeValue(Me.pickuptime.Value) + Me.pickupdate.Value
.Offset(RowCount, 7).Value = Me.cboLiabfnol.Value
.Offset(RowCount, 8).Value = Me.cboTelFnol.Value
.Offset(RowCount, 9).Value = Me.cboAddressfnol.Value
.Offset(RowCount, 10).Value = Me.cboLiabtriage.Value
.Offset(RowCount, 11).Value = Me.cboaddresstriage.Value
.Offset(RowCount, 12).Value = Me.cboNumbertriage.Value
.Offset(RowCount, 13).Value = Me.cboassto.Value
.Offset(RowCount, 14).Value = Me.handoffdate.Value + TimeValue(Me.handofftime.Value)