Solved: Month and Day swap places when OK cmd button is pressed

[christoday]

I've designed a userform to act as an interface between the user and excel spreadsheet database. The userform pulls a record from the spreadsheet and displays in in various textboxes. The user can then amend the details and save it back to the spreadsheet.

However, there is an anomaly whereby the day and month switch places in one particular box when one of the command buttons is pressed.

I've attached my project.

There are two records.

Reference "Post 8000" for "Test Case 1" pulls the data successfully from the spreadsheet but when the "Save record and update on screen" button is pressed, the "Date Received" date changes.

However, the same is not true for "Post 9000" "Test Case 2" which keeps all of it's data as intended when this botton is pressed and I cannot see why the two records behave differently.

Grateful for any help.
thanks

Chris

[mdmackillop]

See here (http://www.vbaexpress.com/forum/showthread.php?t=21067&highlight=isdate) The form is translating values to american format where the date would be valid.

I've applied CDATE to that particular field. You should add it to other date fields.

[Norie]

Chris

Pretty hard to help I'm afraid, especially when it so hard to actually access the code.

Also you've not really told us where the problem is.

Perhaps you should be looking into using DateValue or some other date function(s).

[Aussiebear]

I don't agree Norie. To me, the issue is the formating of the date value. MD's suggestion is a good example on how to get around the problem. If Chris follows MD's advice then it should be solved, should it not?

[christoday]

Indeed, MD's suggestion worked and I added Cdate to all of the date fields accordingly. However, a problem occurs when that field doesnt have a date in it (but may at sometime be added by the user). I get a VB error when, in my example, I delete the date in txtReferred (date received) and click "save and update". Is there a work around so that the form does something else when there's no data to display?

Thanks for your help

Regards, Chris

[mdmackillop]

Using XLD's solution (see link in post 2)

If IsDate(TextBox1.Text) Then
myDate=CDate(TextBox1.Text)
End If

[mdmackillop]

Maybe simplest to add a function

ActiveCell.Offset(0, 3) = FixDate(TxtReferred.Text)



Function FixDate(data)
If IsDate(data) Then
FixDate = CDate(data)
Else
FixDate = data
End If
End Function

[christoday]

Fantastic, this particular problem has been solved.

Thanks mdmackillop and all

[Norie]

I don't agree Norie.
With what?

Did you try downloading the original attachment?:)

[mdmackillop]

With what?

Did you try downloading the original attachment?:)
I did.

I thought the OP was very clear as to the problem, and had no trouble viewing the code.

[Norie]

malcolm

I also downloaded it and was also able to view the code.

But it was far from straightforward, but perhaps that was me.:eek:

In fact, for some reason I seemed to end up with phantom instances of Excel which didn't even appear in the Task Manager.:huh:

And I didn't say the OP wasn't clear as to the problem, and actually suggested
a possible solution which is along the lines of yours. ie convert the 'text' date to a 'real' date.

Anyways, the problems solved so all is good.:)