Unbound text box bonded to date field

[Guest]

HI!

I made a form that asks the user to choose from a list box a criterion. The
form then opens a report with a specific filter. In my report, I have an
unbound text box and I want its control source (a date field from a table) to
automatically change, depending of the filter. (I have more than one date
fields in my table, like DateStatic, DateDynamic, etc.). Formats are all in
short date.
I cant' get the code right for this. I got it right for text boxes bounded
to strings, but not for date.
Can someone please help me?
Thanks a lot

Louis Pat
----------

It's a database on hydrants of a city. Employees will work on a hydrant and
retrieve some data, like its static pressure, dynamic pressure... In my table
I have the different kinds of pressure and the date employees retrieve that
data.

Here is my report's code: (sorry, some names are in french, but I didn't
want to translate all my code) The format property of the unbound text bo is
set to short date too.

Private Sub Report_Open(Cancel As Integer)
Dim sTitle As String
Dim sUnits As String
Dim PressureType As String
Dim sDate As Date

Select Case Forms![bornes exception]!lecture
Case 1
'Title of the report
sTitle = "Bornes d'incendie qui drainent mal ou qui ne drainent
pas"
'Units of the pressure
sUnits = "lbs/ft²"
'Name of the field Pressure type
sPressureType = "[static pressure]"
'Name of the field Date : that's what is not working
sDate = " [date static] " & Format([date static], "short date")

Case 2
......... and so on
..........

End Select
Me.Title.Caption = sTitle
Me.Units.Caption = sUnits
Me.lecture.ControlSource = Pressure
Me.date1.ControlSource = sDate
End Sub

 

[Guest]

sDate = Format([date static], "short date")

 

[Guest]

thanks for replying but it is still not working. With
sDate = Format([date static], "short date")
I get an error: run time error 2465
Microsoft Access can't find the field 'date static' referred to in your
expression

my report is based on a query containing the hydrant number, pressure types
and their dates. I'm pretty sure I didn't mispelled my field names, as I
would have mispelled 6 and Dates are in field list too.

Thanks for help
LouisPat

HI!

I made a form that asks the user to choose from a list box a criterion. The
form then opens a report with a specific filter. In my report, I have an
unbound text box and I want its control source (a date field from a table) to
automatically change, depending of the filter. (I have more than one date
fields in my table, like DateStatic, DateDynamic, etc.). Formats are all in
short date.
I cant' get the code right for this. I got it right for text boxes bounded
to strings, but not for date.
Can someone please help me?
Thanks a lot

Louis Pat
----------

It's a database on hydrants of a city. Employees will work on a hydrant and
retrieve some data, like its static pressure, dynamic pressure... In my table
I have the different kinds of pressure and the date employees retrieve that
data.

Here is my report's code: (sorry, some names are in french, but I didn't
want to translate all my code) The format property of the unbound text bo is
set to short date too.

Private Sub Report_Open(Cancel As Integer)
Dim sTitle As String
Dim sUnits As String
Dim PressureType As String
Dim sDate As Date

Select Case Forms![bornes exception]!lecture
Case 1
'Title of the report
sTitle = "Bornes d'incendie qui drainent mal ou qui ne drainent
pas"
'Units of the pressure
sUnits = "lbs/ft²"
'Name of the field Pressure type
sPressureType = "[static pressure]"
'Name of the field Date : that's what is not working
sDate = " [date static] " & Format([date static], "short date")

Case 2
......... and so on
..........

End Select
Me.Title.Caption = sTitle
Me.Units.Caption = sUnits
Me.lecture.ControlSource = Pressure
Me.date1.ControlSource = sDate
End Sub

 

[Marshall Barton]

A couple of problems here. The Open event is too early to
set a control's Value property, so if you use:
sDate = Format([date static], "short date")
you should move it to the Load event.

OTOH, if you want to set the control's ControlSource
property, the Open event is fine, but you have to set it to
an expression:
sDate = "=Format([date static], ""short date"")"

I think you have something(s?) missnamed in:
Dim PressureType As String
sPressureType = "[static pressure]"
Me.lecture.ControlSource = Pressure
--
Marsh
MVP [MS Access]

thanks for replying but it is still not working. With
sDate = Format([date static], "short date")
I get an error: run time error 2465
Microsoft Access can't find the field 'date static' referred to in your
expression

my report is based on a query containing the hydrant number, pressure types
and their dates. I'm pretty sure I didn't mispelled my field names, as I
would have mispelled 6 and Dates are in field list too.

I made a form that asks the user to choose from a list box a criterion. The
form then opens a report with a specific filter. In my report, I have an
unbound text box and I want its control source (a date field from a table) to
automatically change, depending of the filter. (I have more than one date
fields in my table, like DateStatic, DateDynamic, etc.). Formats are all in
short date.
I cant' get the code right for this. I got it right for text boxes bounded
to strings, but not for date.
----------

It's a database on hydrants of a city. Employees will work on a hydrant and
retrieve some data, like its static pressure, dynamic pressure... In my table
I have the different kinds of pressure and the date employees retrieve that
data.

Here is my report's code: (sorry, some names are in french, but I didn't
want to translate all my code) The format property of the unbound text bo is
set to short date too.

Private Sub Report_Open(Cancel As Integer)
Dim sTitle As String
Dim sUnits As String
Dim PressureType As String
Dim sDate As Date

Select Case Forms![bornes exception]!lecture
Case 1
'Title of the report
sTitle = "Bornes d'incendie qui drainent mal ou qui ne drainent
pas"
'Units of the pressure
sUnits = "lbs/ft²"
'Name of the field Pressure type
sPressureType = "[static pressure]"
'Name of the field Date : that's what is not working
sDate = " [date static] " & Format([date static], "short date")

Case 2
......... and so on
..........

End Select
Me.Title.Caption = sTitle
Me.Units.Caption = sUnits
Me.lecture.ControlSource = Pressure
Me.date1.ControlSource = sDate
End Sub

 

[Guest]

Thanks Marsh!

It doesn't work with sDate = "=Format([date static], ""short date"")"
Me.date1.controlsource= sDate

(I get a type mismatch...)

But if I set the format directly on me.date1, and put this code in my select
case,it works perfectly
Select case
Case 1
Me.Date1.ControlSource = "= Format([date static], ""short date"")"
...

Thanks a lot!!

Louis Pat

A couple of problems here. The Open event is too early to
set a control's Value property, so if you use:
sDate = Format([date static], "short date")
you should move it to the Load event.

OTOH, if you want to set the control's ControlSource
property, the Open event is fine, but you have to set it to
an expression:
sDate = "=Format([date static], ""short date"")"

I think you have something(s?) missnamed in:
Dim PressureType As String
sPressureType = "[static pressure]"
Me.lecture.ControlSource = Pressure
--
Marsh
MVP [MS Access]

thanks for replying but it is still not working. With
sDate = Format([date static], "short date")
I get an error: run time error 2465
Microsoft Access can't find the field 'date static' referred to in your
expression

my report is based on a query containing the hydrant number, pressure types
and their dates. I'm pretty sure I didn't mispelled my field names, as I
would have mispelled 6 and Dates are in field list too.

I made a form that asks the user to choose from a list box a criterion. The
form then opens a report with a specific filter. In my report, I have an
unbound text box and I want its control source (a date field from a table) to
automatically change, depending of the filter. (I have more than one date
fields in my table, like DateStatic, DateDynamic, etc.). Formats are all in
short date.
I cant' get the code right for this. I got it right for text boxes bounded
to strings, but not for date.
----------

It's a database on hydrants of a city. Employees will work on a hydrant and
retrieve some data, like its static pressure, dynamic pressure... In my table
I have the different kinds of pressure and the date employees retrieve that
data.

Here is my report's code: (sorry, some names are in french, but I didn't
want to translate all my code) The format property of the unbound text bo is
set to short date too.

Private Sub Report_Open(Cancel As Integer)
Dim sTitle As String
Dim sUnits As String
Dim PressureType As String
Dim sDate As Date

Select Case Forms![bornes exception]!lecture
Case 1
'Title of the report
sTitle = "Bornes d'incendie qui drainent mal ou qui ne drainent
pas"
'Units of the pressure
sUnits = "lbs/ft²"
'Name of the field Pressure type
sPressureType = "[static pressure]"
'Name of the field Date : that's what is not working
sDate = " [date static] " & Format([date static], "short date")

Case 2
......... and so on
..........

End Select
Me.Title.Caption = sTitle
Me.Units.Caption = sUnits
Me.lecture.ControlSource = Pressure
Me.date1.ControlSource = sDate
End Sub

 

[Marshall Barton]

It doesn't work with sDate = "=Format([date static], ""short date"")"
Me.date1.controlsource= sDate

(I get a type mismatch...)

But if I set the format directly on me.date1, and put this code in my select
case,it works perfectly
Select case
Case 1
Me.Date1.ControlSource = "= Format([date static], ""short date"")"
...

The Type Mismatch is because sDate is declared as Date. If
it were declared a String, the two ways of doing it would be
equivalent.