Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-22-2015, 07:00 AM   #1
FrozenMana
Newly Registered User
 
Join Date: Aug 2015
Posts: 27
Thanks: 5
Thanked 0 Times in 0 Posts
FrozenMana is on a distinguished road
Converting text value into date within query

My first time using this database as my coworker has fallen seriously ill and I am unable to ask them for help.
There is a query that is supposed to translate a text value into a date value. The value for the date is written in a 1yymmdd format in the file/table being used.

The following is the SQL query that is set up:
Code:
SELECT qryValidZips.ZipCode, EMP013.HSAHBCD, Left([HSACHDT],10) AS Expr1, EMP013.HSAOCAT
FROM qryValidZips, qryActiveHubs INNER JOIN (EMP013 INNER JOIN tblRefCoverageCategory ON EMP013.HSAOCAT = tblRefCoverageCategory.CovCatg) ON qryActiveHubs.HBMHBCD = EMP013.HSAHBCD
WHERE (((qryValidZips.ZipCode) Between [hsafrzp] And [hsatozp]))
GROUP BY qryValidZips.ZipCode, EMP013.HSAHBCD, EMP013.HSAOCAT, EMP013.HSASTAT
HAVING (((EMP013.HSASTAT)="ACTV"));

however the query is now giving the error message:
Code:
Undefined function 'Left' in expression

FrozenMana is offline   Reply With Quote
Old 10-22-2015, 07:55 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,242
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Converting text value into date within query

That sounds like a reference problem:

http://www.accessmvp.com/djsteele/Ac...nceErrors.html
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
FrozenMana (10-22-2015)
Old 10-22-2015, 09:59 AM   #3
James Deckert
Continuing to Learn
 
Join Date: Oct 2005
Location: Salina, KS
Posts: 189
Thanks: 0
Thanked 25 Times in 25 Posts
James Deckert is on a distinguished road
Re: Converting text value into date within query

Check Paul's suggestion, but also make sure the VBA compiles ok. Also "compact and repair" the DB.

__________________
HTH,

James Deckert
Access Professional
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Salina, KS
James Deckert is offline   Reply With Quote
Old 10-22-2015, 10:18 AM   #4
FrozenMana
Newly Registered User
 
Join Date: Aug 2015
Posts: 27
Thanks: 5
Thanked 0 Times in 0 Posts
FrozenMana is on a distinguished road
Re: Converting text value into date within query

I thought I updated all of the references. Thank you!
FrozenMana is offline   Reply With Quote
Old 10-22-2015, 12:07 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,242
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Converting text value into date within query

Happy to help!

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
convert date , csv , left function

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help Converting a Date that is defined as Text into a date field so I can calc mbranham General 7 11-07-2014 08:09 PM
Converting text to date Ammarhm Modules & VBA 1 07-26-2011 12:48 PM
Converting text to date Lucia Balkamp Tables 6 11-17-2010 12:04 PM
Converting Text to Date Sashaone Queries 4 01-24-2007 12:50 PM
Converting text to date ya5irha55an Queries 2 07-13-2006 04:47 AM




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


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World