A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

date functions



 
 
Thread Tools Display Modes
  #1  
Old November 7th, 2005, 12:07 PM
kate
external usenet poster
 
Posts: n/a
Default date functions

I have a query that is looking at a text filed showing a date like this
19990431 I need that format changed to be a date field so i can run a start
date and an end date can anyone help me i will appreciate this greatly.

  #2  
Old November 7th, 2005, 01:00 PM
Ofer
external usenet poster
 
Posts: n/a
Default date functions

If the Date field has a fixed size of yyyymmdd you can use this

Select CVDate(Right(FieldName,2) & "/" & Mid(FieldName,5,2) & "/" &
left(FieldName,2)) As DateField From TableName

So the date returned should be 31/04/1999


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


"kate" wrote:

I have a query that is looking at a text filed showing a date like this
19990431 I need that format changed to be a date field so i can run a start
date and an end date can anyone help me i will appreciate this greatly.

  #3  
Old November 7th, 2005, 01:52 PM
John Spencer
external usenet poster
 
Posts: n/a
Default date functions

One method would be

CDate(Format([YourDateField],"@@@@-@@-@@"))

You will get an error with this if the value of YourDateField is null. You
can either test for this with an IIF statement or filter out records where
your date field is null

IIF([YourDateField] is null,
null,CDate(Format([YourDateField],"@@@@-@@-@@")))




"kate" wrote in message
...
I have a query that is looking at a text filed showing a date like this
19990431 I need that format changed to be a date field so i can run a
start
date and an end date can anyone help me i will appreciate this greatly.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Outlook today should show tasks based on Start Date, not due date Rose General Discussion 10 October 9th, 2005 03:14 AM
date ranges from a calculated date field John Feeley Running & Setting Up Queries 1 May 26th, 2005 06:53 AM
Query for 'confirmation' rogge Running & Setting Up Queries 8 April 19th, 2005 03:26 PM
Aggregating Date Data into Weeks and Quarters Roger Running & Setting Up Queries 3 July 11th, 2004 05:56 PM
lost functions Date() and Format$ Ulf Aberg General Discussion 2 June 30th, 2004 12:21 PM


All times are GMT +1. The time now is 12:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.