?
Solved

MS ACCESS text date field compared with datereltoday in query

Posted on 2013-11-14
4
Medium Priority
?
524 Views
Last Modified: 2013-11-14
I have a field SVC_DATE stored as text in this format 20131114
and what I want to do is use a criteria of 'this month' as in current month based on todays date.

I am able to get the month out of the thing with this
SVC_MONTH: Month(Format(Mid([SVC_DATE],5,2) & "/" & Mid([SVC_DATE],7,2) & "/" & Left([SVC_DATE],4),"mm/dd/yyyy"))

And I'm having trouble with setting up the criteria of 'this month'.

I was thinking something like "Like Month(DATERELTODAY(0))" but get a data type mismatch

Thanks,
Mark
0
Comment
Question by:ghettocounselor
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39648581
This month would simply be:

Month(Date())

Is that what you are looking for?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39648591
Based on your expression, I think you would  just need:

SVC_MONTH: Month(Format(Mid([SVC_DATE],5,2) & "/" & Mid([SVC_DATE],7,2) & "/" & Left([SVC_DATE],4),"mm/dd/yyyy"))

And set the criteria

= Month(Date())
0
 

Author Closing Comment

by:ghettocounselor
ID: 39648596
Sweet!
Works as advertised.
Appreciate the quick reply.
0
 
LVL 41

Expert Comment

by:als315
ID: 39648598
You can use DateSerial(Y,M,D) for conversion if you like to use date functions:
DateSerial(Left([SVC_DATE],4), Mid([SVC_DATE],5,2), Mid([SVC_DATE],7,2))
but in you case month is equal to Mid([SVC_DATE],7,2)
and
SVC_MONTH: CCInt(Mid([SVC_DATE],7,2))
should be enough
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question