[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

text to date conversion

Posted on 2006-03-31
4
Medium Priority
?
410 Views
Last Modified: 2008-02-01
I have linked an external DBF file. Within that DBF is a date field that Access sees as a text field. The format is MMDDYY
I want to add that field to a query WHERE value is BETWEEN January 01of the current year and Current date +14
When I use #010106# Access automatically quotes it as text.
How can I make access treat this is a date field? Then how do i use this to reflect a set future date say DATE() +14
0
Comment
Question by:jonoakley
  • 2
4 Comments
 
LVL 15

Expert Comment

by:jmantha709
ID: 16342493
Try :

CDATE([MyDateField])

And

CDATE([MyDateField]) + 14
0
 
LVL 22

Expert Comment

by:Flyster
ID: 16342500
DateValue([YourTextField])

Flyster
0
 
LVL 15

Accepted Solution

by:
jmantha709 earned 2000 total points
ID: 16342541
Or use :

dateserial(RIGHT(TRIM([MyDateField]),2),LEFT(TRIM([MyDateField]),2),MID(TRIM([MyDateField]),3,2))
0
 
LVL 9

Author Comment

by:jonoakley
ID: 16343172
CDATE return was a mess the dates were all over the map Most likely because it was reading a number and converting a number DATEVALUE return was a ERROR# in all fields
 and date serial was just right.
Initially I was working on a 98 box with Access2000 and The CDATE and DATEVALUE would return a compile error
IS there a syntax to using the CDATE function to determine the position of the values?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 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