Originally posted by tonialbrown
Glad you got it resolved.
Re: I changed the line to use the chr(35) and got the same result. Is the below what you meant???
Yes, except that you can use Chr(35) for each/all # delimeters.
-
Tonia,
Glad you got it resolved.
Re: I changed the line to use the chr(35) and got the same result. Is the below what you meant???
Yes, except that you can use Chr(35) for each/all # delimeters. -
HiOriginally posted by puppydogbuddy
I'm really glad this worked for you guys because I can't get this syntax working on my computer. I find this disturbing because I process a lot of dates and thought I knew the business inside out.
I liked you Chr(35) though Jag !
I built a test-bed to check-out this problem of handling Null dates (attached). You will see I have used a DoCmd.RunSQL statement, which compiles every time you take the cursor off the line, so should stop you making silly mistakes.
I have to say that double-quote/single-quote/double-quote causes the remainder of the string to become a comment (turns green)
Neither can I get a date field to accept ## (I've tried all combinations of quotes around it and I've tried Chr(35) & Chr(35))
I can save a Null to a date field but if it comes from an IIf() statement it must be as a "Null" string , which I find curious.
I have a solution that involves evaluating the date and putting the formatted output into a variant type variable, then using the variable in the SQL; all a bit convoluted but it works.
S7Comment
-
I was unable to open the attached file as it is in a later version of Access. I am only using Access 2000 so perhaps that is the difference to why some things are not working.Originally posted by sierra7Comment
-
Hi ToniaOriginally posted by tonialbrown
Please find attached a '2000' database.
On re-reading my posting I seemed to have said "should stop you making silly mistakes..", should have read "should stop me making silly mistakes " :)
If you do read the code under my button, I have commented on some other ways of updating the 'target' table. The first instance (at block 100) uses a SELECT statement and works irrespective of Nulls and formats dates correctly. 'Un-comment' it and try.
At that point I wondered what all of the fuss was about because, as I said, I have been manipulating dates for years and not had any trouble, except with date formatting.
I work from the UK where we put the day before the month and I have found that Access will revert to 'American' dates, wherever possible. I used to have anomalies when testing towards the end of a month and dates based on 31st July would be OK but next day would be interpreted as 8th January.
I had other (different) problems with French dates.
Microsoft's fix (so long ago not to hand) is to always format "yyyy-mm-dd" when calculating or storing in the database. This apparently gives commonality with SQLServer and other databases. When displaying dates in a Form or Report you can re-format as required.
Sorry for rambling. .
S7Comment
-
Hi S7.
I noticed this and I thought you may benefit from (Literal DateTimes and Their Delimiters (#)). The m/d/y format used in databases is actually a SQL standard and mirrors standard American format. This isn't really an Access issue as such.Comment
-
NeoPaOriginally posted by NeoPa
Thank you, that was interesting. It's perhaps not surprising that ANSI would advocate the 'American' style date nor perhaps that ISO 8601 say something different.
Regrettably, I can't turn up the Microsoft article that caused me to revise my developments to use the ISO notation, although I have spent a couple of hours looking on their website. (For my own benefit, not to be argumentative!) What is surprising is there are hundreds of instances where interpretation of dates has caused problems and while most of the solutions involve formatting 'ANSI' others refer to ISO. For instance:-
SQL Server 2005 Books Online
While this may not be directly "an Access issue" it is important that when you format a literal to be stored as a date in a database, that the correct value is stored. Tonia used a format I have not come across before which prompted my comment.
However, this thread was initially about storing a Null date. Tonia seems to have a solution that works for her. I'm still curious why it won't work for me (I've probably got a punctuation wrong somewhere!)
S7Comment
-
S7,
Technical arguments are a good thing. Don't worry :)
As far as dates go in SQL, it's worth remembering that it's only the SQL we're referring to here - not how it's stored or displayed generally within the database. I followed the link, but couldn't see anything to suggest that anything other than the ANSI standards are ever used in any form of SQL.
I've never come across ISO standards pertaining to SQL so I suspect you were talking about standards relating to storage of dates. Please feel free to correct me if I'm mistaken in this.
Now I'm posting anyway, can I ask if you've received the PM I sent you recently?Comment
-
Hello again Tonia - if you are still watching
Looking back through your posts it seems that all you problems stemmed from "How to copy Multiple fields from one record to another existing record". It would appear that you wanted assistance to build an APPEND query but in absence of that help have been struggling on trying to extract data from a list box instead. I have tried to answer how to do that in case you need to refer to it in the future.
Creating an UPDATE query with reference to a form contol (list box) has obviously proved problematic because the need to accommodate Nulls. This particular thread was to address null Dates. I seem to have gone off on a detour with NeoPa because I incorrectly advised about date formats, but that's irrelevant to your particular problem. In fact now that I have read further into the subject your Format() string was more correct than mine.
I refer to an article on the AllenBrown site http://allenbrowne.com/ser-36.html
He suggests writing a 'Wrapper Function' to evaluate date strings for inclusion in SQL. I have done a small modification to take account of Null.
[CODE=vb]
Function SQLDate(varDate As Variant) As String
'Wrapper function by Allen Browne
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrow ne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDa te) = varDate Then
SQLDate = Format$(varDate , "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate , "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
Else 'modified to include "Null"
SQLDate = "Null"
End If
End Function
[/CODE]
I have used this in my test-bed to convert two dates before inserting into my Target table. [CODE=vb]
'600 Use of a Wrapper Function SQLDate() to produce string for SQL statement
DoCmd.RunSQL "INSERT INTO tblTarget ( BoxNum, StartDate, EndDate)" & _
" Values( " & Me!BoxNum & _
"," & SQLDate(StartDa te) & _
"," & SQLDate(EndDate ) & _
" );"
[/CODE]
It does not matter whether these are populated or Null as you can test in the attached sample.
It would appear you have been stuck on this issue for a number of weeks. I hope the remainder of your project will progress more smoothly.
S7
NeoPa
I have responded to your PM. If I find something more to say about the ISO - ANSI issue you will be the first to know! Right now it's like saying you can't use an ISO expression in an ANSI language, which is logical; but even the French have le weekend. I need to find my original source because I didn't make it up!Comment
Comment