Solved

How do you convert 31/12/2011 to 12/31/2011

Posted on 2012-03-22
6
259 Views
Last Modified: 2012-03-26
I have a SQL statement that I run in MS Access 2010 with a WHERE clause on the date.  The date is passed into the module in the format of 31/12/2011, however I need it to be in the format of 12/31/2011.

How do I cahnge the value to 12/31/2011 prior to running the SQL ?
0
Comment
Question by:upobDaPlaya
6 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 37754681
There is not a lot of supporting info here...

Try this:
cdate(format([YourDate],"mm/dd/yyyy"))
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37754695
maybe this:

where mycol = mid(param,4,5) & "/" & left(param,2) & "/" & right(param, 4)
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 166 total points
ID: 37754711
i dont think cdate solves the issue
if you pass "05/07/2011" cdate will not fix it... it will give you wrong date, and format does not help after wrong conversion... so, you should use mid, right, left combination...
is the param always in this format dd/mm/yyyy with zero padded, 10 char all the time?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754748
HainKurt,

Yeah, that was just a shot in the dark...
...wanted to see if it would work without breaking up the date...


Jeff
0
 

Author Comment

by:upobDaPlaya
ID: 37755562
One important variable I left out is that my PC is set to European Format for the regional settings and for the purpose of other work I need to keep it that way..is there a way within VBA to programatically change my settings to US prior to importing my data that contains the dates into my Access table.  

The flow is the date values are imported into MS Access via a MS Acess module from MS Excel.  After the import is completed I run my SQL which has a WHERE clause on the date value.

If not I will try the mid,left,right combo.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 167 total points
ID: 37756196
>  The date is passed into the module in the format of 31/12/2011

Don't know what that means; a date value doesn't carry a specific format, that's something you apply to have it displayed.
If you have the date as a string "31/12/2011", then all you need is to convert this to a date value and then build a formatted string date expression for SQL where the ISO format is preferred:

strDate = "31/12/2011"
strDateSQL = Format(DateValue(strDate), "yyyy/mm/dd")

strSQL = ' Your select statement
strSQL = strSQL & " WHERE [YourDateField] = #" & strDateSQL & "#"

/gustav
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel if Match convert to Access 16 63
Alter an update query which rounds 7 29
Newbie needs help printing from a form. 10 18
Cross Tab with two column values 7 30
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now