please explain [], . and !
in [Queries]
|
Prev: sum a count column in query
Next: Value in union query
From: Phil Smith on 12 Apr 2010 17:31 I think that will work with MySQL as well. Thank you very much. On 4/12/2010 1:29 PM, Bob Barrows wrote: > Use this for SQL Server: > Format(<date-time value>, "yyyymmdd hh:mm:ss") > making sure you delimit it with apostrophes (single quotes), so the > query that gets passed through to sql server looks like this: > " ... WHERE somedatecolumn> '20100201 13:00:00'; " > > I'm not sure what works for MySQL > > Phil Smith wrote: >> I am aware that I need to put my controls into my final queries >> discretely. I have only one problem with that. Dates. >> >> What is the easiest way to convert an Acess date/time field, to a >> straight SQL compatible date as I assemble my query? >> >> Phil >> >> >> >> >> >> >> >> On 4/12/2010 12:55 PM, Bob Barrows wrote: >>> If you're going to be porting to other database systems as you >>> implied in the other post, then use periods instead of the >>> exclamation points, which are more of a VBA artifact. >>> >>> If you do have references to form objects in your queries, you need >>> to be aware that those form objects will not be in the scope of the >>> remote database when used in a passthrough query, so errors will >>> result. You will need to use VBA to replace the reference to the >>> form object in the query's SQL property with the actual value of the >>> form object before running the query. >>> >>> Phil Smith wrote: >>>> Thanks. So is there really no difference between period and >>>> exclamation point, just an aesthetics/visibility issue? Because >>>> Access seems to use it a lot in table. field combinations, and other >>>> SQL engines don't seem to appreciate it... >>>> >>>> I am running into some significant speed issues on some reports, and >>>> am beginning to change some of the queries to passthrough queries. >>>> It is not as simple as I would have hoped to convert these over. >>>> >>>> Learning a lot though... >>>> >>>> Thanx >>>> >>>> >>>> >>>> On 4/12/2010 12:27 PM, John Spencer wrote: >>>>> Brackets are used if the table name or field name is a reserved >>>>> word or contains any characters other than letters, numbers, and >>>>> the underscore. >>>>> >>>>> Why? To avoid problems. Simple example: A field named x-y could be >>>>> interpreted as subtract y from x, so to make sure the the field is >>>>> understood as a field you enter [x-y] which tells the SQL engine >>>>> that this is a field. >>>>> Another simple example Date. Date is a function that returns the >>>>> current date, [Date] is a field. >>>>> >>>>> Period versus Exclamation. The preferred method is to use the >>>>> period when separating table and field names. The only time I use >>>>> the ! is when I am referencing controls and not table and field >>>>> names. >>>>> >>>>> John Spencer >>>>> Access MVP 2002-2005, 2007-2010 >>>>> The Hilltop Institute >>>>> University of Maryland Baltimore County >>>>> >>>>> Phil Smith wrote: >>>>>> When I look at SQL versions of code created in design view, I see >>>>>> many different ways of referencing fields in a table. Please >>>>>> explain the differences and why to use what... >>>>>> >>>>>> >>>>>> table.field >>>>>> >>>>>> [table].[field] >>>>>> >>>>>> [table].field >>>>>> >>>>>> table!field >>>>>> >>>>>> [table]![field] >>>>>> >>>>>> [table]!field >>>>>> >>>>>> >>>>>> Phil > |