Microsoft Access Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > Microsoft Access Help
Reload this Page

problem with SQL query

Discuss problem with SQL query in the Microsoft Access Help forum on ASP Free.
problem with SQL query Microsoft Access forum discussing questions and answers for the Access database. MS Access is used to handle small to medium database needs.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old March 15th, 2004, 06:00 AM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
problem with SQL query
i have the following query in Access DB

Code:
SELECT COUNT(fldDate) AS c 
FROM tblSchedule 
WHERE fldDate=2/5/2004;


now fldDate in tblSchedule has the data 02/05/2004 (SHORT DATE format)

however the query brings back a result of 0, when it should bring back 1.

i have tried the american format date in the query (5/2/2004) but it still results 0.

anyone know why this does not bring back a result of 1 ??
Reply With Quote
  #2  
Old March 15th, 2004, 07:55 AM
umac6 umac6 is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 2 umac6 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Try putting #between your date :
example : flddate=#02/01/2004#
Reply With Quote
  #3  
Old March 15th, 2004, 07:58 AM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
yeah that seems to make it work now...

however it only works when the date in the query is in the US format.... even though the date is in UK format in the actual record.

is it possible to make the query work with UK format date..ie dd/mm/yyyy ?
Reply With Quote
  #4  
Old March 15th, 2004, 10:32 AM
sbaxter sbaxter is offline
Moderator: Access, SQL
ASP Free God (5000 - 5499 posts)
  
Join Date: Oct 2003
Posts: 5,126 sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13
I don't believe that it is possible, directly in a query,

However, if you create a froms with a text box (that is formated with a UK date format) and pass the value entered into the text box into the query, in theory it should work (at that stage a date is a date and access will know how to interpert it.)

Give it a try

S-
Reply With Quote
  #5  
Old March 15th, 2004, 10:53 AM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
that's what i am doing.. passing it thru a form... however the forms passes thru a UK date... ie 2/5/2004... the query doesn't match that in the DB .. as it only seems to match 5/2/2004 !
Reply With Quote
  #6  
Old March 15th, 2004, 10:58 AM
sbaxter sbaxter is offline
Moderator: Access, SQL
ASP Free God (5000 - 5499 posts)
  
Join Date: Oct 2003
Posts: 5,126 sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13
I'm assuming that the regional settings on your computer is set to UK date format correct?

S-
Reply With Quote
  #7  
Old March 15th, 2004, 11:16 AM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
yes that is correct.
Reply With Quote
  #8  
Old March 15th, 2004, 11:24 AM
sbaxter sbaxter is offline
Moderator: Access, SQL
ASP Free God (5000 - 5499 posts)
  
Join Date: Oct 2003
Posts: 5,126 sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13
While I'm thinking about this..

What part of Scotland?

S-
Reply With Quote
  #9  
Old March 15th, 2004, 11:25 AM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
north ayrshire.
Reply With Quote
  #10  
Old March 15th, 2004, 11:35 AM
sbaxter sbaxter is offline
Moderator: Access, SQL
ASP Free God (5000 - 5499 posts)
  
Join Date: Oct 2003
Posts: 5,126 sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13
Just curious,

My Grandparents are from Scotland
Grandpa - around Glasgow
Grandma - around Edinburgh



Sorry - look at following article (you have to convert dates to US formats)

Microsoft Knowledge Base Article - 210069


S-
Reply With Quote
  #11  
Old March 15th, 2004, 12:24 PM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
hmm.. sounds nasty!!!

not keen on changing the dates to US format !
afterall.. it is a UK film schedule the page is displaying (ASP) so i don't want it showing US dates !
Reply With Quote
  #12  
Old March 15th, 2004, 01:50 PM
sbaxter sbaxter is offline
Moderator: Access, SQL
ASP Free God (5000 - 5499 posts)
  
Join Date: Oct 2003
Posts: 5,126 sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13
You dont' have to display them, just use them

Good Luck

S-
Reply With Quote
  #13  
Old March 15th, 2004, 02:02 PM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
i'm confused now
Reply With Quote
  #14  
Old March 15th, 2004, 03:41 PM
sbaxter sbaxter is offline
Moderator: Access, SQL
ASP Free God (5000 - 5499 posts)
  
Join Date: Oct 2003
Posts: 5,126 sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level)sbaxter User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13
You program the interface to show and display the dates in UK format. Then you use a function to convert them to US format for querying purposes only (everything behind the scenes).

S-
Reply With Quote
  #15  
Old March 15th, 2004, 03:55 PM
chenks chenks is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Location: Ayrshire, Scotland
Posts: 9 chenks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to chenks Send a message via AIM to chenks Send a message via MSN to chenks Send a message via Yahoo to chenks
hmm.. will have to sit down and think that one thru..

i am not *that* clued up on access yet !
Reply With Quote
Reply

Viewing: ASP Free Forums > Database > Microsoft Access Help > problem with SQL query

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


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 On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump