Microsoft Access SQL Query

Can I write an SQL query in MS Access 2002?

Specifically, "SELECT * from Workorder where DateEntered > 3/1/2014".

I am accustomed to using MySQL where the date is stored yyyy-mm-dd; not sure if the Access date format is correct as I stated it.

If I can do this, how do I build it (in Access) & Run it? Can I store the "result set" as a table?

Thanks
Richard KortsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
if [DateEntered] is DateTime field you need to enclose the date value in #

"SELECT * from Workorder where DateEntered > #3/1/2014#"
0
Richard KortsAuthor Commented:
OK, but I'm not familiar with how / where to compose the query, etc.

I used Access, YEARS ago.

Thanks
0
PatHartmanCommented:
Dates in Access and most other RDBMS' are not stored as strings.  They are stored as double precision numbers where the integer portion represents the number of days since a particular date which is referred to as the origin date.  The decimal portion represents the time since midnight.

print cdbl(now())
 41844.5865393519
print now()
7/24/2014 2:04:41 PM
So for the above example, there have been 41844 days since Dec 30, 1899 which is what Access uses as the origin date.   Other RDBMS' and also Excel and Word use the same concept but different origin dates.  

To create a query against a linked table in Access, the simplest method is to just open the query designer.  The opening dialog lets you choose tables and/or queries.  Once they are selected, close the dialog.  Then draw join lines between all the tables/queries you selected.  Next, choose the columns you want from each table/query.  And the final step is to add any criteria if necessary.  So, in your case, you would put:

> #3/1/2014#

in the criteria cell beneath the DateEntered field.

The format of a date is ONLY relevant to people.  Access defaults to the windows default date format which in the US is normally "mm/dd/yyyy".  In other countries it could be "dd/mm/yyyy".  In either case, the data is stored internally the same way as a double precision number.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richard KortsAuthor Commented:
I can't just write a simple SQL Query in Access?
0
PatHartmanCommented:
That IS how you create a query in Access.  If you prefer, you can switch to SQL view and type it yourself.  If you are talking about running a query in code, you can write whatever SQL string you want or run a saved querydef.  Keep in mind that saved querydefs are more efficient than embedded SQL since the first time the querydef runs, Access calculates an execution plan and saves it.  Access then reuses the execution plan each time you run the query.  If the SQL is embedded in code, there is no way for Access to save an execution plan so each time you run the query, Access has to recalculate the execution plan.

I suggested the querydef (QBE) as the path of least resistance.  If you are not familiar with Access, you are unlikely to be familiar with its version of SQL syntax.  Using a visual interface eliminates the typing and syntax errors.
0
Richard KortsAuthor Commented:
OK, it was pretty easy with the Query Builder.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.