just a question

  • Thread starter Thread starter Hermie
  • Start date Start date
Hello Rick

I have an expression like: =IIf(DatePart("m",[DATE_GESTION]=True And
DatePart("yyyy",[DATE_GESTION]=True)),DLookUp("[meses]","meses","idmes = " &
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION]),"NEE")

I want to know if this is just a function or a SQL statement

Herman
 
There is no SQL in that expression. IIf, DatePart, DLookUp are all
functions.

paraphrased/restated:
If Date_Gestion is a date, return the value of [field] meses from "table"
meses where field idmes = "month de year" of Date_Gestion. If Date_Gestion
isn't a date, return NEE.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hermie said:
Hello Rick

I have an expression like: =IIf(DatePart("m",[DATE_GESTION]=True And
DatePart("yyyy",[DATE_GESTION]=True)),DLookUp("[meses]","meses","idmes = "
&
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION]),"NEE")

I want to know if this is just a function or a SQL statement

Herman
Rick B said:
Ok, what is the statement? What are you asking here?
Click to expand...
Click to expand...
 
Note that DatePart returns integers so:
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION])
would return "1 de 2005", "2 de 2005" for DATE_GESTION values falling in
January and February 2005.

If idmes values aren't in that format, nothing will ever get returned.


--
George Nicholson

Remove 'Junk' from return address.



George Nicholson said:
There is no SQL in that expression. IIf, DatePart, DLookUp are all
functions.

paraphrased/restated:
If Date_Gestion is a date, return the value of [field] meses from "table"
meses where field idmes = "month de year" of Date_Gestion. If Date_Gestion
isn't a date, return NEE.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hermie said:
Hello Rick

I have an expression like: =IIf(DatePart("m",[DATE_GESTION]=True And
DatePart("yyyy",[DATE_GESTION]=True)),DLookUp("[meses]","meses","idmes =
" &
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION]),"NEE")

I want to know if this is just a function or a SQL statement

Herman
Rick B said:
Ok, what is the statement? What are you asking here?


hello

I like to know if a expression is just a function or an SQL
statement?

Herman
Click to expand...
Click to expand...
Click to expand...
 
Hello George

Many thanks for your answer, I needed this because my colleague said it is
an SQL statement and I said it is not,
BTW this expression works perfectly I use it in my report header to show the
month and year of the report

Herman

George Nicholson said:
There is no SQL in that expression. IIf, DatePart, DLookUp are all
functions.

paraphrased/restated:
If Date_Gestion is a date, return the value of [field] meses from "table"
meses where field idmes = "month de year" of Date_Gestion. If Date_Gestion
isn't a date, return NEE.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hermie said:
Hello Rick

I have an expression like: =IIf(DatePart("m",[DATE_GESTION]=True And
DatePart("yyyy",[DATE_GESTION]=True)),DLookUp("[meses]","meses","idmes = "
&
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION]),"NEE")

I want to know if this is just a function or a SQL statement

Herman
Rick B said:
Ok, what is the statement? What are you asking here?


hello

I like to know if a expression is just a function or an SQL
statement?

Herman
Click to expand...
Click to expand...
Click to expand...
 
George Nicholson said:
Note that DatePart returns integers so:
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION])
would return "1 de 2005", "2 de 2005" for DATE_GESTION values falling in
January and February 2005.

If idmes values aren't in that format, nothing will ever get returned.


--
George Nicholson

Remove 'Junk' from return address.



George Nicholson said:
There is no SQL in that expression. IIf, DatePart, DLookUp are all
functions.

paraphrased/restated:
If Date_Gestion is a date, return the value of [field] meses from "table"
meses where field idmes = "month de year" of Date_Gestion. If Date_Gestion
isn't a date, return NEE.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hermie said:
Hello Rick

I have an expression like: =IIf(DatePart("m",[DATE_GESTION]=True And
DatePart("yyyy",[DATE_GESTION]=True)),DLookUp("[meses]","meses","idmes =
" &
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION]),"NEE")

I want to know if this is just a function or a SQL statement

Herman
"Rick B" <Anonymous> wrote in message
Ok, what is the statement? What are you asking here?


hello

I like to know if a expression is just a function or an SQL
statement?

Herman
Click to expand...
Click to expand...
Click to expand...
 
DLookup could be viewed as similar to a SQL function (if there were such a
thing), but it is really a method of the Access Application object. It is
no more SQL than Excel's DGET function is.

DatePart is a VisualBasic function
iif is part of VBA

--
George Nicholson

Remove 'Junk' from return address.


Hermie said:
Hello George

Many thanks for your answer, I needed this because my colleague said it is
an SQL statement and I said it is not,
BTW this expression works perfectly I use it in my report header to show
the
month and year of the report

Herman

George Nicholson said:
There is no SQL in that expression. IIf, DatePart, DLookUp are all
functions.

paraphrased/restated:
If Date_Gestion is a date, return the value of [field] meses from
"table"
meses where field idmes = "month de year" of Date_Gestion. If
Date_Gestion
isn't a date, return NEE.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hermie said:
Hello Rick

I have an expression like: =IIf(DatePart("m",[DATE_GESTION]=True And
DatePart("yyyy",[DATE_GESTION]=True)),DLookUp("[meses]","meses","idmes
= "
&
DatePart("m",[DATE_GESTION])) & " de " &
DatePart("yyyy",[DATE_GESTION]),"NEE")

I want to know if this is just a function or a SQL statement

Herman
"Rick B" <Anonymous> wrote in message
Ok, what is the statement? What are you asking here?


hello

I like to know if a expression is just a function or an SQL
statement?

Herman
Click to expand...
Click to expand...
Click to expand...
 
George said:
DLookup could be viewed as similar to a SQL function (if there were such a
thing)
Click to expand...

SQL does have functions, of course. The ones that immediately spring to
mind are the set functions (e.g. COUNT, MAX, AVG etc) and the niladic
functions (e.g. CURRENT_TIMESTAMP). See the Jet SQL documentation:

http://office.microsoft.com/en-us/assistance/CH010410331033.aspx
but it is really a method of the Access Application object. It is
no more SQL than Excel's DGET function is.

DatePart is a VisualBasic function
iif is part of VBA
Click to expand...

There is a difference between using VBA functions and MS Access
functions in Jet SQL, which is apparent for those of use who use Jet
outside of the MS Access UI. Most of the VBA5 functions (i.e. in the
traditional sense, as distinct from 'methods'), including your examples
IIf and DatePart, are native to Jet 4. VBA6-only functions, such as
InStrRev, and functions/methods of the MS Access object model are only
available through appropriate versions of the MS Access UI.

To call on my earlier example, Jet 4 does not support the ANSI-92
standard CURRENT_TIMESTAMP function, so I have to use the Now() or
Date() functions in my queries. Therefore, I assert that these VBA5
functions found in Jet independently of the MS Access UI may be
considered Jet SQL functions.

Jamie.

--
 
All valid points. I shouldn't have thrown that misleading parenthetical in
there.
 
Back
Top