Problem with Format in Recordsource sql (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 04:44
Joined
Oct 30, 2008
Messages
1,028
I've changed a SubForms recordsource from



Select TheDate,....
to
"SELECT Format([TheDate],'dd mmm yyyy') AS ChartDate,...


But then the field becomes #Name?
However if I paste the query into design mode and run it, it is okay.
The form is datasheet and I don't know where the Format property for the field.
Can I get around this anyhow?
 

vba_php

Forum Troll
Local time
Today, 09:44
Joined
Oct 6, 2019
Messages
2,884
did you see the thread where Richard stated earlier that using single quote marks is not recognized by Access' internal processing system? you have to use double quotes, always (other than in special situations in VBA). I have not looked at FORMAT() in a while either, but are you sure this is right?
Code:
'dd mmm yyyy'
wouldn't it have to have "/" symbols in it instead of spaces?
Code:
"dd/mm/yyyy"
as a side note to this, you should be thankful you don't work with javascript or PHP. the acceptance of singles and doubles in those languages can make a person pull their hair out! ;)
 

kirkm

Registered User.
Local time
Tomorrow, 04:44
Joined
Oct 30, 2008
Messages
1,028
Hey Adam, I also tried replacing the single quote with double ones... but no differnce.

Yes, "dd mmm yyyy" is OK. I use it everywhere.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:44
Joined
Jan 20, 2009
Messages
12,174
I've changed a SubForms recordsource from

Select TheDate,....
to
"SELECT Format([TheDate],'dd mmm yyyy') AS ChartDate,...


But then the field becomes #Name?

Did you change the ControlSource of the textbox to ChartDate ?
 

kirkm

Registered User.
Local time
Tomorrow, 04:44
Joined
Oct 30, 2008
Messages
1,028
No Galaxion I hadn't - and thats fixed it nicely.
Thanks :)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:44
Joined
Jan 20, 2009
Messages
12,174
did you see the thread where Richard stated earlier that using single quote marks is not recognized by Access' internal processing system? you have to use double quotes, always (other than in special situations in VBA).

In my experience Access has always been quite happy with either double or single quotes provided of course that they are in pairs. Access certainly recognises the Format String in single quotes. Single quotes also definitely work for query criteria.

I have not looked at FORMAT() in a while either, but are you sure this is right?
Code:
'dd mmm yyyy'
wouldn't it have to have "/" symbols in it instead of spaces?
Code:
"dd/mm/yyyy"

Not a matter of how long it is since you looked but just something you never actually understood. Anything can go inside the format string. If the character is not one of those that represents a DatePart then it will be included literally. DatePart characters can be included literally by escaping with a backslash.

Your contribution to this thread has been a distraction with nothing but completely misleading advice. You do this far too often in your rush to be the first to post, often on subjects you clearly know very little about.

Please check your suggestions before you post instead of wasting other peoples' time and polluting threads with wrong or misleading advice.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:44
Joined
Feb 28, 2001
Messages
18,768
did you see the thread where Richard stated earlier that using single quote marks is not recognized by Access' internal processing system?

I think you overstated the selectivity. I'll admit it can get tricky, but it ain't nuclear physics.

Once something is inside double-quotes, apostrophe works as interior quoting marks. There are issues if you pass something double-quoted that contains a single-quoted portion into a sub/function and then use concatenation on the entire argument. Since double quotes often get stripped that way, that is usually when apostrophe rears its ugly head - because if the VBA parser sees a naked apostrophe, it treats it as the start of a comment.

However, in the context shown by KirkM, the quoting wasn't wrong. Note that he was tossing that apostrophe pair inside a "SELECT" query string, which VBA would NOT have had to handle. And SQL would have treated it right.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:44
Joined
Jan 20, 2009
Messages
12,174
BTW SQL Server does care about single or double quotes depending on the setting QUOTED_IDENTIFIERS.

If the setting is ON then Single quote is a string or date delimiter and double quotes can be used as object name delimiters. It will accept either single or double quotes if the setting is off but it is on by default.

In some circumstances QUOTED_IDENTIFIERS must be ON which is why it is included in many of the automatic scripts generated by Management Studio.
 

Users who are viewing this thread

Top Bottom