A string problem

  • Thread starter Thread starter ericb
  • Start date Start date
E

ericb

I have a form on which is a subform and 5 commands buttons.

On the click of a button, I want to change the records shown on the subform.
I am having a problem with one of those buttons.

One is suppose to show records of costumers that came between 2 dates. To do
so I made a table with 3 fields :ID, date_1, date_2.
date_1 and date_2 are date/time variables.

I also made a small form that changes those dates and with an OK button.

The command button opens the small date subform.

On the click of the OK button on the small subform I go into this procedure :

' A défini les dates et va updater la subform avec les records sur une
période de temps
Private Sub OK_Click()

'Ma query
Dim mySQL As String
Dim date_debut As String
Dim date_fin As String
Dim cmdWhere As String

date_debut = [date_1].Value
date_fin = [date_2].Value
cmdWhere = " WHERE (((Produit.date_livraison) Between " + date_debut + " And
" + date_fin + "))"
'cmdWhere = " WHERE (((Produit.date_livraison) Between [date_low] And [
date_high]))"

'to view the string
MsgBox cmdWhere

mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client=Produit.REF_client"
mySQL = mySQL + cmdWhere
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

Forms![Home Base]![Liste Client HB subform].Form.RecordSource = mySQL
'Me![Liste Client HB subform].Form.FilterOn = True

' Ferme le formulaire et sauve les dates de la période de temps de recherche
'DoCmd.Close acForm, "Introduire la période de temps", acSaveYes

End Sub

The present cmdWhere those not work and the commented one does. When I look
at the strings they both look the same but results are not the same, why?

I would like to use the subform to change the dates and not have Access
prompt for two dates.

Thank you for the help.
 
You need to include the date delimiters and format the date in either US
format (mm-dd-yyyy) or in the unambiguous format of "yyyy-mm-dd"

Dim strFormat as string

strFormat ="\#yyyy-mm-dd\#"

cmdWhere = " WHERE Produit.date_livraison Between " +
Format(date_debut,strFormat) + " And
" + Format(date_fin,strFormat) + ""

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Unfortunately i'm still having problems with this problem.

Here is the code again :

Private Sub OK_Click()

'Ma query
Dim mySQL As String
Dim date_debut As String
Dim date_fin As String
Dim cmdWhere As String
Dim strFormat As String

strFormat = "\#dd/mm/yyyy\#"

date_debut = [date_1].Value
date_fin = [date_2].Value

'cmdWhere = " WHERE (((Produit.date_livraison) Between [date_low] And
[date_high]))"
'cmdWhere = " WHERE (((Produit.date_livraison) Between " +
Format(date_debut, strFormat) + " And [date_high]))"
cmdWhere = " WHERE (((Produit.date_livraison) Between " + Format(date_debut,
strFormat) + " And " + Format(date_fin, strFormat) + "))"


MsgBox cmdWhere

mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client=Produit.REF_client"
mySQL = mySQL + cmdWhere
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

Forms![Home Base]![Liste Client HB subform].Form.RecordSource = mySQL
'Me![Liste Client HB subform].Form.FilterOn = True

' Ferme le formulaire et sauve les dates de la période de temps de recherche
DoCmd.Close acForm, "Introduire la période de temps", acSaveYes

End Sub


I changed the strFormat from "\#dd-mm-yyyy\#" to "\#dd/mm/yyyy\#" because
all my dates are written this way.

Now if i use first cmdWhere all is OK, i get 2 records. With the second I
get an extra record that is not suppose to show up. With the third cmdWhere i
get 0, no records at all.

I tried with and without the Format() function and it does not help.

My problem is I want ot use the third cmWhere. How do I work over this
problem ?

Thank you for the help.
--
eric


John Spencer said:
You need to include the date delimiters and format the date in either US
format (mm-dd-yyyy) or in the unambiguous format of "yyyy-mm-dd"

Dim strFormat as string

strFormat ="\#yyyy-mm-dd\#"

cmdWhere = " WHERE Produit.date_livraison Between " +
Format(date_debut,strFormat) + " And
" + Format(date_fin,strFormat) + ""

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a form on which is a subform and 5 commands buttons.

On the click of a button, I want to change the records shown on the subform.
I am having a problem with one of those buttons.

One is suppose to show records of costumers that came between 2 dates. To do
so I made a table with 3 fields :ID, date_1, date_2.
date_1 and date_2 are date/time variables.

I also made a small form that changes those dates and with an OK button.

The command button opens the small date subform.

On the click of the OK button on the small subform I go into this procedure :

' A défini les dates et va updater la subform avec les records sur une
période de temps
Private Sub OK_Click()

'Ma query
Dim mySQL As String
Dim date_debut As String
Dim date_fin As String
Dim cmdWhere As String

date_debut = [date_1].Value
date_fin = [date_2].Value
cmdWhere = " WHERE (((Produit.date_livraison) Between " + date_debut + " And
" + date_fin + "))"
'cmdWhere = " WHERE (((Produit.date_livraison) Between [date_low] And [
date_high]))"

'to view the string
MsgBox cmdWhere

mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client=Produit.REF_client"
mySQL = mySQL + cmdWhere
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

Forms![Home Base]![Liste Client HB subform].Form.RecordSource = mySQL
'Me![Liste Client HB subform].Form.FilterOn = True

' Ferme le formulaire et sauve les dates de la période de temps de recherche
'DoCmd.Close acForm, "Introduire la période de temps", acSaveYes

End Sub

The present cmdWhere those not work and the commented one does. When I look
at the strings they both look the same but results are not the same, why?

I would like to use the subform to change the dates and not have Access
prompt for two dates.

Thank you for the help.
Click to expand...
.
Click to expand...
 
strFormat = "\#dd/mm/yyyy\#"
Click to expand...

Reread John's note.

The programmers who developed Access were Americans, and used the American
date convention, "\#mm/dd/yyyy\#".

Change your strFormat to this and your query should work.
 
Ericb -

If you are going to use Between on string dates, then they must be formatted
in yyyymmdd order for the comparison to work. You also need to format your
produit.date_livraison in the same format. (From a string comparison
standpoint, 02/12/2009 is between 01/01/2010 and 03/01/2010.)

strFormat = "yyyy/mm/dd"

cmdWhere = " WHERE (((Format(Produit.date_livraison, "yyyy/mm/dd")) Between
" + Format(date_debut,
strFormat) + " And " + Format(date_fin, strFormat) + "))"

Try these changes. If it doesn't work, let us know what is wrong (wrong
records returned, error compiling, etc.)
--
Daryl S


ericb said:
Unfortunately i'm still having problems with this problem.

Here is the code again :

Private Sub OK_Click()

'Ma query
Dim mySQL As String
Dim date_debut As String
Dim date_fin As String
Dim cmdWhere As String
Dim strFormat As String

strFormat = "\#dd/mm/yyyy\#"

date_debut = [date_1].Value
date_fin = [date_2].Value

'cmdWhere = " WHERE (((Produit.date_livraison) Between [date_low] And
[date_high]))"
'cmdWhere = " WHERE (((Produit.date_livraison) Between " +
Format(date_debut, strFormat) + " And [date_high]))"
cmdWhere = " WHERE (((Produit.date_livraison) Between " + Format(date_debut,
strFormat) + " And " + Format(date_fin, strFormat) + "))"


MsgBox cmdWhere

mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client=Produit.REF_client"
mySQL = mySQL + cmdWhere
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

Forms![Home Base]![Liste Client HB subform].Form.RecordSource = mySQL
'Me![Liste Client HB subform].Form.FilterOn = True

' Ferme le formulaire et sauve les dates de la période de temps de recherche
DoCmd.Close acForm, "Introduire la période de temps", acSaveYes

End Sub


I changed the strFormat from "\#dd-mm-yyyy\#" to "\#dd/mm/yyyy\#" because
all my dates are written this way.

Now if i use first cmdWhere all is OK, i get 2 records. With the second I
get an extra record that is not suppose to show up. With the third cmdWhere i
get 0, no records at all.

I tried with and without the Format() function and it does not help.

My problem is I want ot use the third cmWhere. How do I work over this
problem ?

Thank you for the help.
--
eric


John Spencer said:
You need to include the date delimiters and format the date in either US
format (mm-dd-yyyy) or in the unambiguous format of "yyyy-mm-dd"

Dim strFormat as string

strFormat ="\#yyyy-mm-dd\#"

cmdWhere = " WHERE Produit.date_livraison Between " +
Format(date_debut,strFormat) + " And
" + Format(date_fin,strFormat) + ""

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a form on which is a subform and 5 commands buttons.

On the click of a button, I want to change the records shown on the subform.
I am having a problem with one of those buttons.

One is suppose to show records of costumers that came between 2 dates. To do
so I made a table with 3 fields :ID, date_1, date_2.
date_1 and date_2 are date/time variables.

I also made a small form that changes those dates and with an OK button.

The command button opens the small date subform.

On the click of the OK button on the small subform I go into this procedure :

' A défini les dates et va updater la subform avec les records sur une
période de temps
Private Sub OK_Click()

'Ma query
Dim mySQL As String
Dim date_debut As String
Dim date_fin As String
Dim cmdWhere As String

date_debut = [date_1].Value
date_fin = [date_2].Value
cmdWhere = " WHERE (((Produit.date_livraison) Between " + date_debut + " And
" + date_fin + "))"
'cmdWhere = " WHERE (((Produit.date_livraison) Between [date_low] And [
date_high]))"

'to view the string
MsgBox cmdWhere

mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client=Produit.REF_client"
mySQL = mySQL + cmdWhere
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

Forms![Home Base]![Liste Client HB subform].Form.RecordSource = mySQL
'Me![Liste Client HB subform].Form.FilterOn = True

' Ferme le formulaire et sauve les dates de la période de temps de recherche
'DoCmd.Close acForm, "Introduire la période de temps", acSaveYes

End Sub

The present cmdWhere those not work and the commented one does. When I look
at the strings they both look the same but results are not the same, why?

I would like to use the subform to change the dates and not have Access
prompt for two dates.

Thank you for the help.
Click to expand...
.
Click to expand...
Click to expand...
 
Thank you for the help.

Finally it comes down to :

Private Sub OK_Click()

Dim mySQL As String
Dim strFormat As String

'Pour reformater mes dates à l'Americaine
strFormat = "\#yyyy/mm/dd\#"

' Batir la commande SQL
mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client=Produit.REF_client"
mySQL = mySQL + " WHERE (((Produit.date_livraison) Between " +
Format([date_1].Value, strFormat) + " And " + Format([date_2].Value,
strFormat) + "))"
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

' Change la source des données
Forms![Home Base]![Liste Client HB subform].Form.RecordSource = mySQL

' Change la couleur du bouton à Orange
Forms![Home Base]!cmdEntre2dates.BackColor = RGB(255, 182, 51)

' Ferme le formulaire et sauve les dates de la période de temps de recherche
DoCmd.Close acForm, "Introduire la période de temps", acSaveYes

End Sub


I have one last question :

Why does this work well and I do not use the Format() function on the field
Produit.date_livraison in the WHERE part of mySQL ?

It is funny because all the dates and input masks are written like this
dd/mm/yyyy. So what I understand now is that it compares a date dd/mm/yyyy
and yyyy/mm/dd.

I guess things do move in mysterious ways.
 
Why does this work well and I do not use the Format() function on the field
Produit.date_livraison in the WHERE part of mySQL ?

It is funny because all the dates and input masks are written like this
dd/mm/yyyy. So what I understand now is that it compares a date dd/mm/yyyy
and yyyy/mm/dd.
Click to expand...

The date is actually stored as a number, a count of days and fractions of a
day (times) since midnight, December 30, 1899. If you're just entering a date
into a textbox on a form, or into a field in a table, or as a typed-in
criterion, Access will translate it from what you type ("01/08/10") into a
date (January 8, 2010) according to your computer's Regional date/time
settings.

It's when you use a literal text string in code or in SQL that you must
provide either the American (month/day/year, yes I know it's illogical!) or an
unambiguous format (such as yyyy-mm-dd).
 
Back
Top