Solved

MS ACCESS text date field compared with datereltoday in query

Posted on 2013-11-14
4
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 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 40

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 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