Filter on date problem

Hi
I am trying to display records whose ring-back-date is today.

Here is my code:

    Dim strDate As String
    strDate = Format(Date, "dd/mm/yyyy")
    Me.Filter = "[Ring-back-date] = " & strDate
    Me.FilterOn = True

The field [Ring-back-date] is defined as Short Date, input mask
00/00/0000;;_

Stapes
0
Stapes
1/3/2008 1:44:47 PM
access 16762 articles. 3 followers. Follow

8 Replies
668 Views

Similar Articles

[PageSpeed] 49

On Thu, 3 Jan 2008 05:44:47 -0800 (PST), Stapes
<steve.staple@gmail.com> wrote:

You're not stating what problem (if any) you are receiving, any error
messages?
I'm assuming the filter does not work because you don't have #-signs
around the date:
Me.Filter = "[Ring-back-date] = #" & strDate & "#"

Also make sure you understand the difference between a variable or
field of type Date, and a formatted date value. Formatting has nothing
to do with how Date values are stored, but is stictly a presentation
issue.

-Tom.



>Hi
>I am trying to display records whose ring-back-date is today.
>
>Here is my code:
>
>    Dim strDate As String
>    strDate = Format(Date, "dd/mm/yyyy")
>    Me.Filter = "[Ring-back-date] = " & strDate
>    Me.FilterOn = True
>
>The field [Ring-back-date] is defined as Short Date, input mask
>00/00/0000;;_
>
>Stapes
0
Tom
1/3/2008 2:24:28 PM
You cannot use your own local date format in the Filter of the string: it 
needs to be in the format expected by JET. It also needs # around it as the 
delimiter:
    Dim strWhere As String
    If Me.Dirty Then Me.Dirty = False    'Save first.
    strWhere = "[Ring-back-date] = " & Format(Date, "\#mm/dd/yyyy\#")
    Me.Filter = strWhere
    Me.FilterOn = True

If your date field may have a time component in it, use:
    strWhere = "([Ring-back-date] >= " & Format(Date, "\#mm/dd/yyyy\#") & ") 
AND ([Ring-back-date] < " & Format(Date + 1, "\#mm/dd/yyyy\#")

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stapes" <steve.staple@gmail.com> wrote in message
news:eee965c6-5786-47b0-8d8f-6442ba29192b@c4g2000hsg.googlegroups.com...
> Hi
> I am trying to display records whose ring-back-date is today.
>
> Here is my code:
>
>    Dim strDate As String
>    strDate = Format(Date, "dd/mm/yyyy")
>    Me.Filter = "[Ring-back-date] = " & strDate
>    Me.FilterOn = True
>
> The field [Ring-back-date] is defined as Short Date, input mask
> 00/00/0000;;_
>
> Stapes 

0
Allen
1/3/2008 2:24:28 PM
On Thu, 3 Jan 2008 23:24:28 +0900, "Allen Browne"
<AllenBrowne@SeeSig.Invalid> wrote:

Good point that I missed: regardless of your locale, Jet works with
the US date format for queries.
-Tom.


>You cannot use your own local date format in the Filter of the string: it 
>needs to be in the format expected by JET. It also needs # around it as the 
>delimiter:
>    Dim strWhere As String
>    If Me.Dirty Then Me.Dirty = False    'Save first.
>    strWhere = "[Ring-back-date] = " & Format(Date, "\#mm/dd/yyyy\#")
>    Me.Filter = strWhere
>    Me.FilterOn = True
>
>If your date field may have a time component in it, use:
>    strWhere = "([Ring-back-date] >= " & Format(Date, "\#mm/dd/yyyy\#") & ") 
>AND ([Ring-back-date] < " & Format(Date + 1, "\#mm/dd/yyyy\#")
0
Tom
1/3/2008 2:47:34 PM
On Jan 3, 7:47=A0am, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
> On Thu, 3 Jan 2008 23:24:28 +0900, "Allen Browne"
>
> <AllenBro...@SeeSig.Invalid> wrote:
>
> Good point that I missed: regardless of your locale, Jet works with
> the US date format for queries.
> -Tom.
>
>
>
> >You cannot use your own local date format in the Filter of the string: it=

> >needs to be in the format expected by JET. It also needs # around it as t=
he
> >delimiter:
> > =A0 =A0Dim strWhere As String
> > =A0 =A0If Me.Dirty Then Me.Dirty =3D False =A0 =A0'Save first.
> > =A0 =A0strWhere =3D "[Ring-back-date] =3D " & Format(Date, "\#mm/dd/yyyy=
\#")
> > =A0 =A0Me.Filter =3D strWhere
> > =A0 =A0Me.FilterOn =3D True
>
> >If your date field may have a time component in it, use:
> > =A0 =A0strWhere =3D "([Ring-back-date] >=3D " & Format(Date, "\#mm/dd/yy=
yy\#") & ")
> >AND ([Ring-back-date] < " & Format(Date + 1, "\#mm/dd/yyyy\#")- Hide quot=
ed text -
>
> - Show quoted text -

If [Ring-back-date] is a short date formated date field then I believe
the following should probably work also:

strWhere =3D "[Ring-back-date] =3D #" & date() & "#"



0
Ron2006
1/3/2008 3:40:15 PM
Ron, I think you missed the points in the reply:

a) If the fields is formatted as Short Date, that's a really good way to 
confuse the user about the presence of the time component. Setting the 
Format property does not prevent a time being entered (typically because the 
Default value was set to Now() instead of Date()); it just prevents Access 
from displaying it. But the query will not return any records when you ask 
for the particular date (except those entered at exactly midnight.)

b) The return value of Date() is the system date in your local format. In my 
country, it returns 4/1/2008 today. If you append that into the string (as 
in your example), the query will return records for April Fools Day, not 4th 
January. For more inforamation about that, see:
    International Date Formats in Access
at:
    http://allenbrowne.com/ser-36.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron2006" <ronnemec@hotmail.com> wrote in message
news:8244ed83-9aba-4a6f-a692-d6719a70c23f@h11g2000prf.googlegroups.com...
On Jan 3, 7:47 am, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
> On Thu, 3 Jan 2008 23:24:28 +0900, "Allen Browne"
>
> <AllenBro...@SeeSig.Invalid> wrote:
>
> Good point that I missed: regardless of your locale, Jet works with
> the US date format for queries.
> -Tom.
>
>
>
> >You cannot use your own local date format in the Filter of the string: it
> >needs to be in the format expected by JET. It also needs # around it as 
> >the
> >delimiter:
> > Dim strWhere As String
> > If Me.Dirty Then Me.Dirty = False 'Save first.
> > strWhere = "[Ring-back-date] = " & Format(Date, "\#mm/dd/yyyy\#")
> > Me.Filter = strWhere
> > Me.FilterOn = True
>
> >If your date field may have a time component in it, use:
> > strWhere = "([Ring-back-date] >= " & Format(Date, "\#mm/dd/yyyy\#") & ")
> >AND ([Ring-back-date] < " & Format(Date + 1, "\#mm/dd/yyyy\#")- Hide 
> >quoted text -
>
> - Show quoted text -

If [Ring-back-date] is a short date formated date field then I believe
the following should probably work also:

strWhere = "[Ring-back-date] = #" & date() & "#"



0
Allen
1/4/2008 12:48:47 AM
I was not addressing a time component. in which case all bets are off
and you are absolutely right.

If a field is a date ie formated as a date and displayed as a date and
being treated as a date isn't it really a number and therefore the
comparison would work, no matter what the external format? Especially
with the # signs telling it that the enclosed item is a date.

I have not had to work with other than US formated dates and so have
not been bit by the problem. Just trying to glean knowledge from those
who have encountered the problem.

Ron
0
Ron2006
1/4/2008 1:32:22 PM
A Date/Time value is internally stored as a special kind of floating point 
number - that's correct.

The # delimiter informs JET that the value is to be read as a date. However, 
JET interprets the format of a literal date in a string as being American 
(mdy) if it can. That way, a query statement generates the same results 
regardless of where it is executed. The trouble is that the output of Date() 
is presented to the user in their local date format. Therefore if you merely 
concatenate the string output of Date() into a string, and then use the 
string in a query statement, you will get the wrong results if you live in a 
country that does not use the US date format.

I realize this is a non-issue for many Americans who just make databases for 
their own use. But it is a serious issue for developers whose software could 
be used in the UK/Australia (dmy), Korea (ymd), etc.

Same issues apply to literal date values in VBA code.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ron2006" <ronnemec@hotmail.com> wrote in message
news:25b4ff3f-206e-4489-9f67-b1623497df5e@e4g2000hsg.googlegroups.com...
>
> If a field is a date ie formated as a date and displayed as a date and
> being treated as a date isn't it really a number and therefore the
> comparison would work, no matter what the external format? Especially
> with the # signs telling it that the enclosed item is a date.
>
> I have not had to work with other than US formated dates and so have
> not been bit by the problem. Just trying to glean knowledge from those
> who have encountered the problem.
>
> Ron 

0
Allen
1/4/2008 2:59:50 PM
Thanks for the explanation.

I shall add it to my notes table, and hope I remember it if I
encounter a problem.

Ron
0
Ron2006
1/4/2008 5:42:57 PM
Reply:

Similar Artilces:

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

Customize Current View: Filter Problem
Hello, Using Outlook 2002, I have created a category called "Hidden" i outlook. In my calendar, I put all the appointments I don't want t appear in this calendar (i.e. daily reminders). I then filter out all these "Hidden" items by selecting -- View--> Current View--> Customize Current View--> Filter--> More Choices--> Categories--> Hidden (the category I created)--> OK--> SQL--> Edit these criteria directly--> And then I change: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AN ("urn:schemas-microsoft...

CSV File Problem
Can any one explain why the following problem occurs please? A colleague has a small list of data which is a csv file exported from SQL. Column One is a serial number from 1 to 29. Column 2 is a number of transactions which have occurred.(it should look like example A below in Excel) 1 475 1,475 2 1732 21,732 3 1670 31,670 4 2176 42,176 5 608 5,608 6 1579 61,579 7 43101 743,101 8 54512 854,512 9 51258 951,258 10 61050 1,061,050 A B If I double click on the csv file, it opens in Excel but c...

Month problem
I have some code where I calculate this month minus 2 month. This goes fine until I get to august. When I use DateSerial to deduct 2 month I get to July, and this makes no sence. I made the following testcode. Sub StrangeMonth() Dim MyDate As Date MyDate = #8/31/2010# MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 1, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 2, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart(&qu...

Rounding Problem
I am using the following formula: ROUND((BH24-BG24)*24,1) Where BH24 = 15:21, BG24 = 15:00 (times based on a 24 hour clock) The answer returned is 0.3, but it should be 0.4. If you round out to 2 places the answer is 0.35. Unless I am missing something 0.35 should round to 0.4. If I use Roundup, it rounds everything up. What am I missing? Hi this is due to Excel's representation of numbers (see: http://www.cpearson.com/excel/rounding.htm) In your case the formula =(BH24-BG24)*24 does not return 0.35 but 0.3499999999999 (Just test it and format the resulting cell with enough decima...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Offline folder problems #2
Hi Guys, Basically I installed Outlook 97 on my office PC which is connect to microsoft exchange server. For some reason all of my messages were copied into offline folders, this causes a problem, whenever I open up outlook I get this message. Upload of offline changes could not be completed you do not have sufficient permission to perform this operation on this object. See the folder contact or see your system administrator. I do not administrator access on my user profile, every time I open outlook it resets everything I have changed (view etc) and gives that error message. How ...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Filters in Excel 2003
I am using Filters in Excel 2003. Every cell in sheet B is linked to every cell in sheet A. That way I can alter data in B without corrupting the original data in A. I then highlight the cells in B that I want to assign a filter to and select Advanced Filter. I have no criteria so I do not set that. I click OK. I then select Filter again and this time select Auto Filter. Drop down arrows appear at the top of my columns. Great, no problems so far, I then select from the drop down list the criteria that I want to filter. Again no problems, my list filters correctly. But once I have do...

Resolving Email Address to existing contact problem
Has anyone else come across this issue in MSCRM3.0? - when you attempt to resolve an email address to an existing contact in CRM (that does not currently have an email address stored in their details) the contact resolves OK but does not update the email address in the contacts details. Is this by design, or have I found a bug? ...

outlook 2k2 problem
I am using Outlook 2002 with SP3, and i have not had any problems up until a week ago. My password does not seem to save even though i have typed it in correctly in the e-mail acct settings ,and have checked "save password. It saves it as long as i have Outlook open, but as soon as i close it, and then later open it again, my password is gone and Outlook asks me for my e-mail password. So, i re-type and i check "save password". But if i decide to close Outlook (say for the evening, and shut down my computer or something), my password disappears the next time i open Outlo...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

OLK 2k7
Outlook is behaving strangly with the "through the selected account" option. Each time I restart Outlook the rule fails. When I go in to check on the rules I get told that the rule is "invalid". and the "SELECTED" account is no longer selected. Each time the criteria the account needs to be selected by changes. For example with the following data Account Name Email Account mailserver.domain1.com user@domain1.com mailserver.domain2.com user@domain2.com One time I go in and it's asking me to select the account ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Hyperlink problem #3
I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

Mother of a sumproduct (ish) problem!
At least it is to me - now I humbly beseech your magic :) A1 to A10 contain 10 unique letters, e.g. A,Q,E... B1 to B10 contain 10 unique letters, e.g. Z,B,A... C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q... D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B... E1 to E100 contain values, e.g. 9,1,3,5,7,2... In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for the sample...

Spam is a big big problem
Spam is a big big problem ...

CRM 3.0 Login Problems
Some specific users are constantly getting prompted for CRM login in Outlook. We are using desktop client (online) online. This happens randomly. We have CRM 3.0 with rollup update 2 and IE7. We have also applied this fix http://support.microsoft.com/default.aspx/kb/934243/en-us. Also added the crm site to local intranet zone. Please help. Thanks. set the authentication in IE check rollup update 2 -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "raj" wrote: >...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Problem with Database Wizard
I'm trying to generate a diagram based on the contents of an Access database, using the database to provide x,y coords for instances of a Master shape. It seems I need to run the wizard twice, first to link a master, then to generate the drawing. The first bit appears to work OK, but when I do the second bit, Visio says that there is no master in the stencil that it can use. But I know the master is connected, because if I modify the database, then refresh the shapes, they change accordingly. Does anyone have any idea why this isn't working for me ? I'm using Visio 20...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Strange problem
We have been rolling out new Windows 7 workstations (all new computers) at work over the last few months and a strange problem has developed. Prior to Windows 7 we were using Windows XP with Office 2007. The problem is, we have a workbook which uses a UserForm for filling in all required information. Prior to rolling out the new Windows 7 workstations, the workbook and UserForm were working flawlessly (for over a year) and they still work flawlessly on the first 3 Windows 7 workstations but on the last 2 workstations we rolled out if you open the UserForm and leave it open for ...