Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 25th, 2004, 11:39 AM
Authorized User
 
Join Date: Jul 2003
Location: Franklin, Indiana, USA.
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query String Cast or Convert function

I am using the following query:

SELECT [Data and Names_Crosstab].Date,
[Data and Names_Crosstab].Diamond,
[Data and Names_Crosstab].Opponent,
[Data and Names_Crosstab].Record,
[Data and Names_Crosstab].Result,
[Data and Names_Crosstab].Time
FROM [Data and Names_Crosstab]
ORDER BY [Data and Names_Crosstab].Date;

The original table from which [Data and Names_Crosstab].Date is selected is formatted as a text field. The reason for this is long and convoluted, but necessary. My ORDER BY is therefore doing a text sort on the dates rather than a date sort.

In my SQL Server books I have found CAST and CONVERT functions that I believe would work to correct the sort thus:

ORDER BY (CAST([Data and Names_Crosstab].Date, Date/Time));

CAST, however, is an "Undefined Function" in Access. Is my assumption above correct about how CAST would work?

How do I achieve the same functionality in Access?

Thanks in advance.

Rich


__________________
Ego is a faithful friend; He stays with us all the way to the crater.
Reply With Quote
  #2 (permalink)  
Old February 25th, 2004, 11:52 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Depends on what is actually in the field but you might try CDate. I believe Date and Time are reserved words in Access so these fields should be:
Code:
SELECT [Data and Names_Crosstab].[Date], 
[Data and Names_Crosstab].Diamond, 
[Data and Names_Crosstab].Opponent, 
[Data and Names_Crosstab].Record, 
[Data and Names_Crosstab].Result, 
[Data and Names_Crosstab].[Time]
FROM [Data and Names_Crosstab]
ORDER BY CDate([Data and Names_Crosstab].[Date])
--

Joe
Reply With Quote
  #3 (permalink)  
Old February 25th, 2004, 12:00 PM
Authorized User
 
Join Date: Jul 2003
Location: Franklin, Indiana, USA.
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That worked just as advertised. I searched online and in book indices for date format, format, convert, case, cast, etc. and never came upon "CDate". If one does not know that "CDate" exists, how should I have gone about finding it???

Thanks a bunch!!!
Rich

Reply With Quote
  #4 (permalink)  
Old February 25th, 2004, 12:10 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Well it's not a legitimate ANSI SQL function, nor an Access one either. It works because Access allows you to use any function in the Visual Basic for Applications (VBA) library. If you go into Modules | New then press F2 and change to VBA in the drop down at the top left you can see all the other ones and then highlight them and press F1 for more information.

--

Joe
Reply With Quote
  #5 (permalink)  
Old February 25th, 2004, 12:12 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

I also tried Google. 'Covert String Date Access' produced a lot of answers in the first few hits.

--

Joe
Reply With Quote
  #6 (permalink)  
Old February 25th, 2004, 12:13 PM
Authorized User
 
Join Date: Jul 2003
Location: Franklin, Indiana, USA.
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again for your help!!

Rich

Reply With Quote
  #7 (permalink)  
Old February 25th, 2004, 01:58 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

If you ever need a function but don't know if one exists, search help for one that DOES exist and then click the "See Also" link. There you may find "Functions" or "Date Functions" or "Text Functions" or some link like that. Clicking it will then give you the list that Access allows.

I have found if you search Access help DIRECTLY for "Date Functions" right off the bat, it won't find what you want, but if you actually search for a known one and click the "See Also" link, you'll get them. Weird, huh?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
CAST or CONVERT in Access owain SQL Language 12 November 28th, 2013 05:56 AM
convert string to function austinf Javascript 3 September 19th, 2008 03:25 AM
CAST function Adam H-W SQL Language 5 May 24th, 2007 04:27 AM
Different between CAST and CONVERT functions jdang67 SQL Server 2000 2 October 24th, 2004 12:00 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM



All times are GMT -4. The time now is 03:47 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.