Pbl with DateAdd with Format in query

[Frankie via AccessMonster.com]

I have the following to achieve :
2 calcualted fields [Souplesse1] and [Souplesse2]on a query meant to return
a date value based on another calculated field [NbreJours] which itself
returns a number value in days to be added to and substracted from [DateFin]
field . The substraction goes to [Souplesse1] and addition goes to
[Souplesse2] and must appear as Date format dd/mm/yyyy.
Searching this newsgroup I have found some info about system date problem.
I did the folowing in my DB:
Created a module:
Option Compare Database
Public Function SQLDate(varDate As Variant) As String
If IsDate(varDate) Then
SQLDate = "#" & Format$(varDate, "dd\/mm\/yyyy") & "#"
End If
End Function

Then I type in the following in the [Souplesse2] field :
Format(AjDate("d";[NbreJours];[T_Missions].[DateFin]);"dd\/mm\/yyyy")
After I validate Access turns it into :
Format(AjDate("\d";[NbreJours];[T_Missions].[DateFin]);"""dd/""mm""/yy""")
So local settings still take over ! And I have an #error message.
Can someone help me understand what the solution to this problem is ?

Thank you in advance.
Frankie
MS Access 2003

 

[Ken Snell [MVP]]

It's not clear what you are doing here.

Try again. Post the SQL statements of all the queries that you're
discussing.

One thing that may be a problem for you is that you're trying to use the
dd/mm/yyyy format between # delimiters for a date. ACCESS requires the US
format for a date string: mm/dd/yyyy.

 

[Frankie via AccessMonster.com]

Sorry if it's not clear enough. There is one query with the following SQL:

SELECT T_FicheContrat.IDContrat, T_FicheContrat.IDMission,
T_FicheContrat.IDInterimaire, T_Interimaires.INom, T_Interimaires.IPrenom,
T_Interimaires.INationalite, T_Interimaires.NumSS, T_Interimaires.IAdresse,
T_Interimaires.IVille, T_Interimaires.ICodePostal, T_BaseClients.IDClients,
T_BaseClients.NomClient, T_BaseClients.AdresseClt,
T_BaseClients.CpltAdresseClt, T_BaseClients.VilleClt, T_BaseClients.[Code
PostalClt], T_Missions.Qualification, T_Missions.Activite,
T_Missions.Departement, T_Missions.DureeparSemaine, T_Missions.NbreSemaine,
DateDiff("d",[T_Missions].[DateDebut],[T_Missions].[DateFin],2) AS
NbreJours, IIf(([NbreJours]/7*5)<=5,1,IIf(([NbreJours]/7*5)<=10,2,IIf((
[NbreJours]/7*5)<=15,3,IIf(([NbreJours]/7*5)<=20,4,IIf(([NbreJours]/7*5)
<=25,5,IIf(([NbreJours]/7*5)<=30,6,IIf(([NbreJours]/7*5)<=35,7,IIf((
[NbreJours]/7*5)<=40,8,IIf(([NbreJours]/7*5)<=45,9,IIf(([NbreJours]/7*5)

45,10,0)))))))))) AS SouplesseJours, Format(DateAdd("\d",[NbreJours],

[T_Missions].[DateFin]),"""mm""/""dd""/""yyyy""") AS SouplesseAvant,
T_Missions.DateDebut, T_Missions.DateFin, T_Missions.Horaires,
T_Missions.TermePrecis, T_Missions.DureeMinimale,
T_Missions.OrgTempsTravail, Requ?tecalculSalaireHoraire.Coefficient,
T_Missions.TreiziemeMois, Requ?tecalculSalaireHoraire.SalaireHoraire,
Requ?tecalculSalaireHoraire.QuotePartT, IIf([NbreSemaine]<4,"2J",IIf(
[NbreSemaine] Between 4 And 8,"3J",IIf([NbreSEmaine] Between 8 And
12,"4J",IIf([NbreSemaine]>12,"5J")))) AS PeriodeEssai,
Requ?tecalculSalaireHoraire.SalaireReference,
Requ?tecalculSalaireHoraire.BaseHoraire12Mois,
Requ?tecalculSalaireHoraire.TarifHoraireHT, Round(Sum([SalaireReference]
*10/100),2) AS IFM, Round(Sum([SalaireReference]*1.1*10/100),2) AS CP,
T_Missions.CarteOrange, T_Missions.Lieu, T_Missions.ContactPers,
T_Missions.MoyenAcces, T_Missions.Article231, T_Missions.Caracteristiques,
T_Missions.CasRecours, T_Missions.DetailRecours
FROM T_BaseClients INNER JOIN (T_Interimaires INNER JOIN ((T_Missions INNER
JOIN Requ?tecalculSalaireHoraire ON T_Missions.IDMission =
Requ?tecalculSalaireHoraire.IDMission) INNER JOIN T_FicheContrat ON
T_Missions.IDMission = T_FicheContrat.IDMission) ON
T_Interimaires.IDInterimaire = T_FicheContrat.IDInterimaire) ON
T_BaseClients.IDClients = T_Missions.IDClient
GROUP BY T_FicheContrat.IDContrat, T_FicheContrat.IDMission,
T_FicheContrat.IDInterimaire, T_Interimaires.INom, T_Interimaires.IPrenom,
T_Interimaires.INationalite, T_Interimaires.NumSS, T_Interimaires.IAdresse,
T_Interimaires.IVille, T_Interimaires.ICodePostal, T_BaseClients.IDClients,
T_BaseClients.NomClient, T_BaseClients.AdresseClt,
T_BaseClients.CpltAdresseClt, T_BaseClients.VilleClt, T_BaseClients.[Code
PostalClt], T_Missions.Qualification, T_Missions.Activite,
T_Missions.Departement, T_Missions.DureeparSemaine, T_Missions.NbreSemaine,
T_Missions.DateDebut, T_Missions.DateFin, T_Missions.Horaires,
T_Missions.TermePrecis, T_Missions.DureeMinimale,
T_Missions.OrgTempsTravail, Requ?tecalculSalaireHoraire.Coefficient,
T_Missions.TreiziemeMois, Requ?tecalculSalaireHoraire.SalaireHoraire,
Requ?tecalculSalaireHoraire.QuotePartT,
Requ?tecalculSalaireHoraire.SalaireReference,
Requ?tecalculSalaireHoraire.BaseHoraire12Mois,
Requ?tecalculSalaireHoraire.TarifHoraireHT, T_Missions.CarteOrange,
T_Missions.Lieu, T_Missions.ContactPers, T_Missions.MoyenAcces,
T_Missions.Article231, T_Missions.Caracteristiques, T_Missions.CasRecours,
T_Missions.DetailRecours
ORDER BY T_Interimaires.INom;

Frankie

 

[Ken Snell [MVP]]

I apologize in advance, but I am still confused about what the problem is
here.

Your first post discusses a public function named SQLDate. This query does
not use that function.

Is this SQL statement from a saved query? Is it an SQL string that you built
in code? Is it an SQL string that you're using to get the final SQL
statement in code?

Your first post talked about " characters being inserted by ACCESS. This
does not happen automatically in the SQL view of a query, nor in VBA code.
It does happen, however, in the Format property of a control or field. Where
are you typing this SQL statement?

Please, provide more details about what and where and how.

--

Ken Snell
<MS ACCESS MVP>

Sorry if it's not clear enough. There is one query with the following SQL:

SELECT T_FicheContrat.IDContrat, T_FicheContrat.IDMission,
T_FicheContrat.IDInterimaire, T_Interimaires.INom, T_Interimaires.IPrenom,
T_Interimaires.INationalite, T_Interimaires.NumSS,
T_Interimaires.IAdresse,
T_Interimaires.IVille, T_Interimaires.ICodePostal,
T_BaseClients.IDClients,
T_BaseClients.NomClient, T_BaseClients.AdresseClt,
T_BaseClients.CpltAdresseClt, T_BaseClients.VilleClt, T_BaseClients.[Code
PostalClt], T_Missions.Qualification, T_Missions.Activite,
T_Missions.Departement, T_Missions.DureeparSemaine,
T_Missions.NbreSemaine,
DateDiff("d",[T_Missions].[DateDebut],[T_Missions].[DateFin],2) AS
NbreJours, IIf(([NbreJours]/7*5)<=5,1,IIf(([NbreJours]/7*5)<=10,2,IIf((
[NbreJours]/7*5)<=15,3,IIf(([NbreJours]/7*5)<=20,4,IIf(([NbreJours]/7*5)
<=25,5,IIf(([NbreJours]/7*5)<=30,6,IIf(([NbreJours]/7*5)<=35,7,IIf((
[NbreJours]/7*5)<=40,8,IIf(([NbreJours]/7*5)<=45,9,IIf(([NbreJours]/7*5)

45,10,0)))))))))) AS SouplesseJours, Format(DateAdd("\d",[NbreJours],

[T_Missions].[DateFin]),"""mm""/""dd""/""yyyy""") AS SouplesseAvant,
T_Missions.DateDebut, T_Missions.DateFin, T_Missions.Horaires,
T_Missions.TermePrecis, T_Missions.DureeMinimale,
T_Missions.OrgTempsTravail, Requ?tecalculSalaireHoraire.Coefficient,
T_Missions.TreiziemeMois, Requ?tecalculSalaireHoraire.SalaireHoraire,
Requ?tecalculSalaireHoraire.QuotePartT, IIf([NbreSemaine]<4,"2J",IIf(
[NbreSemaine] Between 4 And 8,"3J",IIf([NbreSEmaine] Between 8 And
12,"4J",IIf([NbreSemaine]>12,"5J")))) AS PeriodeEssai,
Requ?tecalculSalaireHoraire.SalaireReference,
Requ?tecalculSalaireHoraire.BaseHoraire12Mois,
Requ?tecalculSalaireHoraire.TarifHoraireHT, Round(Sum([SalaireReference]
*10/100),2) AS IFM, Round(Sum([SalaireReference]*1.1*10/100),2) AS CP,
T_Missions.CarteOrange, T_Missions.Lieu, T_Missions.ContactPers,
T_Missions.MoyenAcces, T_Missions.Article231, T_Missions.Caracteristiques,
T_Missions.CasRecours, T_Missions.DetailRecours
FROM T_BaseClients INNER JOIN (T_Interimaires INNER JOIN ((T_Missions
INNER
JOIN Requ?tecalculSalaireHoraire ON T_Missions.IDMission =
Requ?tecalculSalaireHoraire.IDMission) INNER JOIN T_FicheContrat ON
T_Missions.IDMission = T_FicheContrat.IDMission) ON
T_Interimaires.IDInterimaire = T_FicheContrat.IDInterimaire) ON
T_BaseClients.IDClients = T_Missions.IDClient
GROUP BY T_FicheContrat.IDContrat, T_FicheContrat.IDMission,
T_FicheContrat.IDInterimaire, T_Interimaires.INom, T_Interimaires.IPrenom,
T_Interimaires.INationalite, T_Interimaires.NumSS,
T_Interimaires.IAdresse,
T_Interimaires.IVille, T_Interimaires.ICodePostal,
T_BaseClients.IDClients,
T_BaseClients.NomClient, T_BaseClients.AdresseClt,
T_BaseClients.CpltAdresseClt, T_BaseClients.VilleClt, T_BaseClients.[Code
PostalClt], T_Missions.Qualification, T_Missions.Activite,
T_Missions.Departement, T_Missions.DureeparSemaine,
T_Missions.NbreSemaine,
T_Missions.DateDebut, T_Missions.DateFin, T_Missions.Horaires,
T_Missions.TermePrecis, T_Missions.DureeMinimale,
T_Missions.OrgTempsTravail, Requ?tecalculSalaireHoraire.Coefficient,
T_Missions.TreiziemeMois, Requ?tecalculSalaireHoraire.SalaireHoraire,
Requ?tecalculSalaireHoraire.QuotePartT,
Requ?tecalculSalaireHoraire.SalaireReference,
Requ?tecalculSalaireHoraire.BaseHoraire12Mois,
Requ?tecalculSalaireHoraire.TarifHoraireHT, T_Missions.CarteOrange,
T_Missions.Lieu, T_Missions.ContactPers, T_Missions.MoyenAcces,
T_Missions.Article231, T_Missions.Caracteristiques, T_Missions.CasRecours,
T_Missions.DetailRecours
ORDER BY T_Interimaires.INom;

Frankie

 

[Frankie via AccessMonster.com]

I am the one who should apologise for not beeing clear enough.
Beeing a newbie, I get confused with what I am trying to do.
I 'll try again:
How should I call or do I need to call the SQL Date function from the SQL
Statement (saved Query) posted before ?? This saved query feeds a report
with date fields among other information. I want this report to show the
dates calculation explained earlier. So I inserted a calculated field
within the saved query set to Expression and typed the DateDiff Sql
statement in the field area which produces this error.
What I sent you is the SQL view of this query.
If I completely misunderstood this Date thing please let me know.

 

[Ken Snell [MVP]]

I don't see exactly where in your SQL string you want to call the function,
but calling a public, user-defined function from a query is very simple. You
just use the name of the function and the ( ) characters for the arguments
that you're sending to the function. If there are no arguments, you still
need to use the ( ) characters so that ACCESS will know that you're calling
a function by that name and not trying to use a field with that name.

For example:

WHERE MyDateField = SQLDate([AnotherDateField])