Domain argument in DSum

[Paul3rd]

Hello, is it possible to set the domain argument of a DSum expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Douglas J. Steele]

I'm not sure I follow how a combo box named cboApptDate would related to a
domain name, but yes, you can use a variable or a reference to a function
that returns a string in all of the domain aggregate functions.

 

[Paul3rd]

Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

I'm not sure I follow how a combo box named cboApptDate would related to a
domain name, but yes, you can use a variable or a reference to a function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, is it possible to set the domain argument of a DSum expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Douglas J. Steele]

No offense, but if you've got multiple tables all alike, with names like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the value
elsewhere in the routine). If you do need FRSum, don't declare it as String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

I'm not sure I follow how a combo box named cboApptDate would related to
a
domain name, but yes, you can use a variable or a reference to a function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, is it possible to set the domain argument of a DSum expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Paul3rd]

Hello Doug,
I took your advice and put all records in a single table [tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row, tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it were a
data entry form. (it's not). When I add a record it does go to the table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

No offense, but if you've got multiple tables all alike, with names like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the value
elsewhere in the routine). If you do need FRSum, don't declare it as String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

I'm not sure I follow how a combo box named cboApptDate would related to
a
domain name, but yes, you can use a variable or a reference to a function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Douglas J. Steele]

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Doug,
I took your advice and put all records in a single table [tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row, tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it were a
data entry form. (it's not). When I add a record it does go to the table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

No offense, but if you've got multiple tables all alike, with names like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the value
elsewhere in the routine). If you do need FRSum, don't declare it as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Paul3rd]

Hello Doug,
cboApptDate contains date only.
format of date in cboApptDate is 'Short Date'
Inserting CDate generated an error code saying the expression was to complex
to be evaluated.
The problem is definitely in the WHERE clause, if I remove it entirely
from the criteria of the query then all of the records show in the form.
If I remove [Forms] from [Forms]![SnglApptDis]![cboApptDate] and am forced
to manually enter the parameters for cboApptDate, then the appropriate
records
are displayed.
I feel like I'm really close to solving this with your help.
Thanks again
Paul

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Doug,
I took your advice and put all records in a single table [tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row, tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it were a
data entry form. (it's not). When I add a record it does go to the table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

No offense, but if you've got multiple tables all alike, with names like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the value
elsewhere in the routine). If you do need FRSum, don't declare it as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Douglas J. Steele]

I just reread your post a little closer. Since the combo box is on the same
form, when the form opens, nothing going to be selected in the combo box, so
it makes sense that nothing's display.

For the AfterUpdate event of the combo box, try the following Event
Procedure:

Private Sub cboApptDate_AfterUpdate()

Me.Requery

End Sub

so that the form's recordsource gets updated every time you select something
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
cboApptDate contains date only.
format of date in cboApptDate is 'Short Date'
Inserting CDate generated an error code saying the expression was to
complex
to be evaluated.
The problem is definitely in the WHERE clause, if I remove it entirely
from the criteria of the query then all of the records show in the form.
If I remove [Forms] from [Forms]![SnglApptDis]![cboApptDate] and am forced
to manually enter the parameters for cboApptDate, then the appropriate
records
are displayed.
I feel like I'm really close to solving this with your help.
Thanks again
Paul

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE
CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Doug,
I took your advice and put all records in a single table [tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row, tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it
were a
data entry form. (it's not). When I add a record it does go to the
table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

:

No offense, but if you've got multiple tables all alike, with names
like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in
it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the value
elsewhere in the routine). If you do need FRSum, don't declare it as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would
related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Paul3rd]

Hello Doug,
That didn't work. No records were returned.
Could I go another way and use a ApplyFilter command
to the OnOpen event of the form?
I'd still have to have the cboApptDate box set the value for
the filters WHERE clause.
Something like:
"(SELECT[ApptDate]FROM[tblApptDis]
WHERE([ApptDate]=#" & Me.cboApptDate & "#))"
Which does not work by the way.
Paul

I just reread your post a little closer. Since the combo box is on the same
form, when the form opens, nothing going to be selected in the combo box, so
it makes sense that nothing's display.

For the AfterUpdate event of the combo box, try the following Event
Procedure:

Private Sub cboApptDate_AfterUpdate()

Me.Requery

End Sub

so that the form's recordsource gets updated every time you select something
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
cboApptDate contains date only.
format of date in cboApptDate is 'Short Date'
Inserting CDate generated an error code saying the expression was to
complex
to be evaluated.
The problem is definitely in the WHERE clause, if I remove it entirely
from the criteria of the query then all of the records show in the form.
If I remove [Forms] from [Forms]![SnglApptDis]![cboApptDate] and am forced
to manually enter the parameters for cboApptDate, then the appropriate
records
are displayed.
I feel like I'm really close to solving this with your help.
Thanks again
Paul

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE
CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello Doug,
I took your advice and put all records in a single table [tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row, tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it
were a
data entry form. (it's not). When I add a record it does go to the
table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

:

No offense, but if you've got multiple tables all alike, with names
like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in
it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the value
elsewhere in the routine). If you do need FRSum, don't declare it as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would
related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Paul3rd]

Hello Doug,
I placed the following code in the OnOpen event of the form:
Dim qryApptDis As QueryDefs
Me.cboApptDate.Value = Date
With Forms!SnglApptDis
Form.RecordSource = "qryApptDis"
End With
The form then did open with the correct records.
However, the cboApptDate box won't still won't change the WHERE clause
of the qryApptDis.
I placed the following code in the AfterUpdate event of the cboApptDate box:
Dim qryApptDis As QueryDefs
With Forms!SnglApptDis
Form.RecordSource = "qryApptDis" & Me.cboApptDate.Value
End With
Me.Requery
It compiled OK, but did not change the displayed records.
Should I have placed it somewhere else, or is the Form.RecordSource
statement wrong?
Paul

I just reread your post a little closer. Since the combo box is on the same
form, when the form opens, nothing going to be selected in the combo box, so
it makes sense that nothing's display.

For the AfterUpdate event of the combo box, try the following Event
Procedure:

Private Sub cboApptDate_AfterUpdate()

Me.Requery

End Sub

so that the form's recordsource gets updated every time you select something
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
cboApptDate contains date only.
format of date in cboApptDate is 'Short Date'
Inserting CDate generated an error code saying the expression was to
complex
to be evaluated.
The problem is definitely in the WHERE clause, if I remove it entirely
from the criteria of the query then all of the records show in the form.
If I remove [Forms] from [Forms]![SnglApptDis]![cboApptDate] and am forced
to manually enter the parameters for cboApptDate, then the appropriate
records
are displayed.
I feel like I'm really close to solving this with your help.
Thanks again
Paul

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE
CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello Doug,
I took your advice and put all records in a single table [tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row, tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it
were a
data entry form. (it's not). When I add a record it does go to the
table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

:

No offense, but if you've got multiple tables all alike, with names
like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in
it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the value
elsewhere in the routine). If you do need FRSum, don't declare it as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would
related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Douglas J. Steele]

A filter is a Where clause without the word "WHERE", so you'd want to set
the filter to

"ApptDate = " & Format(Me.cboApptDate, "\#yyyy\-mm\-dd\#")

(see my September, 2003 "Access Answers" column in Pinnacle Publication's
"Smart Access" for an explanation of why to use that format. You can
download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
That didn't work. No records were returned.
Could I go another way and use a ApplyFilter command
to the OnOpen event of the form?
I'd still have to have the cboApptDate box set the value for
the filters WHERE clause.
Something like:
"(SELECT[ApptDate]FROM[tblApptDis]
WHERE([ApptDate]=#" & Me.cboApptDate & "#))"
Which does not work by the way.
Paul

I just reread your post a little closer. Since the combo box is on the
same
form, when the form opens, nothing going to be selected in the combo box,
so
it makes sense that nothing's display.

For the AfterUpdate event of the combo box, try the following Event
Procedure:

Private Sub cboApptDate_AfterUpdate()

Me.Requery

End Sub

so that the form's recordsource gets updated every time you select
something
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
cboApptDate contains date only.
format of date in cboApptDate is 'Short Date'
Inserting CDate generated an error code saying the expression was to
complex
to be evaluated.
The problem is definitely in the WHERE clause, if I remove it entirely
from the criteria of the query then all of the records show in the
form.
If I remove [Forms] from [Forms]![SnglApptDis]![cboApptDate] and am
forced
to manually enter the parameters for cboApptDate, then the appropriate
records
are displayed.
I feel like I'm really close to solving this with your help.
Thanks again
Paul
:

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE
CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello Doug,
I took your advice and put all records in a single table
[tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only
the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row,
tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it
were a
data entry form. (it's not). When I add a record it does go to the
table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

:

No offense, but if you've got multiple tables all alike, with names
like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in
it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the
value
elsewhere in the routine). If you do need FRSum, don't declare it
as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would
related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain
name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Douglas J. Steele]

With Forms!SnglApptDis
Form.RecordSource = "qryApptDis"
End With

need only be:

Forms!SnglApptDis.RecordSource = "qryApptDis"

Because what's in the With...End With construct doesn't start with a .,
you're not actually using the With construct!

I have no idea what

"qryApptDis" & Me.cboApptDate.Value

is supposed to be.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
I placed the following code in the OnOpen event of the form:
Dim qryApptDis As QueryDefs
Me.cboApptDate.Value = Date
With Forms!SnglApptDis
Form.RecordSource = "qryApptDis"
End With
The form then did open with the correct records.
However, the cboApptDate box won't still won't change the WHERE clause
of the qryApptDis.
I placed the following code in the AfterUpdate event of the cboApptDate
box:
Dim qryApptDis As QueryDefs
With Forms!SnglApptDis
Form.RecordSource = "qryApptDis" & Me.cboApptDate.Value
End With
Me.Requery
It compiled OK, but did not change the displayed records.
Should I have placed it somewhere else, or is the Form.RecordSource
statement wrong?
Paul

I just reread your post a little closer. Since the combo box is on the
same
form, when the form opens, nothing going to be selected in the combo box,
so
it makes sense that nothing's display.

For the AfterUpdate event of the combo box, try the following Event
Procedure:

Private Sub cboApptDate_AfterUpdate()

Me.Requery

End Sub

so that the form's recordsource gets updated every time you select
something
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
cboApptDate contains date only.
format of date in cboApptDate is 'Short Date'
Inserting CDate generated an error code saying the expression was to
complex
to be evaluated.
The problem is definitely in the WHERE clause, if I remove it entirely
from the criteria of the query then all of the records show in the
form.
If I remove [Forms] from [Forms]![SnglApptDis]![cboApptDate] and am
forced
to manually enter the parameters for cboApptDate, then the appropriate
records
are displayed.
I feel like I'm really close to solving this with your help.
Thanks again
Paul
:

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE
CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello Doug,
I took your advice and put all records in a single table
[tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only
the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row,
tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it
were a
data entry form. (it's not). When I add a record it does go to the
table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

:

No offense, but if you've got multiple tables all alike, with names
like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in
it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the
value
elsewhere in the routine). If you do need FRSum, don't declare it
as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would
related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain
name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul

 

[Paul3rd]

Hello Doug,
SUCESS!
Here is the final solution.
I didn't have to make any changes to the query.

Private Sub Form_Open(Cancel As Integer)
'This code works correctly opening form on correct Record(s)
Dim qryApptDis As QueryDefs
Me.cboApptDate.Value = Date
Forms!SnglApptDis.RecordSource = "qryApptDis"
End Sub

Private Sub NewPopUp_Click()
'This code is in the Click event of the Calendar control and
'correctly modifies the query to return the correct record(s).
Dim qryApptDis As QueryDefs
Forms!SnglApptDis!cboApptDate = Me.NewPopUp.Value
Forms![SnglApptDis].RecordSource = "qryApptDis"
Me.Form.Visible = False
End Sub

Thanks again for your help,
Paul

With Forms!SnglApptDis
Form.RecordSource = "qryApptDis"
End With

need only be:

Forms!SnglApptDis.RecordSource = "qryApptDis"

Because what's in the With...End With construct doesn't start with a .,
you're not actually using the With construct!

I have no idea what

"qryApptDis" & Me.cboApptDate.Value

is supposed to be.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,
I placed the following code in the OnOpen event of the form:
Dim qryApptDis As QueryDefs
Me.cboApptDate.Value = Date
With Forms!SnglApptDis
Form.RecordSource = "qryApptDis"
End With
The form then did open with the correct records.
However, the cboApptDate box won't still won't change the WHERE clause
of the qryApptDis.
I placed the following code in the AfterUpdate event of the cboApptDate
box:
Dim qryApptDis As QueryDefs
With Forms!SnglApptDis
Form.RecordSource = "qryApptDis" & Me.cboApptDate.Value
End With
Me.Requery
It compiled OK, but did not change the displayed records.
Should I have placed it somewhere else, or is the Form.RecordSource
statement wrong?
Paul

I just reread your post a little closer. Since the combo box is on the
same
form, when the form opens, nothing going to be selected in the combo box,
so
it makes sense that nothing's display.

For the AfterUpdate event of the combo box, try the following Event
Procedure:

Private Sub cboApptDate_AfterUpdate()

Me.Requery

End Sub

so that the form's recordsource gets updated every time you select
something
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello Doug,
cboApptDate contains date only.
format of date in cboApptDate is 'Short Date'
Inserting CDate generated an error code saying the expression was to
complex
to be evaluated.
The problem is definitely in the WHERE clause, if I remove it entirely
from the criteria of the query then all of the records show in the
form.
If I remove [Forms] from [Forms]![SnglApptDis]![cboApptDate] and am
forced
to manually enter the parameters for cboApptDate, then the appropriate
records
are displayed.
I feel like I'm really close to solving this with your help.
Thanks again
Paul
:

Does ApptDate contain date only, or date and time?

What's the format of the date in the combo box?

You could try changing your Where clause to:

WHERE
CDate(tblApptDis.ApptDate)=CDate([Forms]![SnglApptDis]![cboApptDate])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello Doug,
I took your advice and put all records in a single table
[tblApptDis],
and added a column that contains a date field[apptdate].
I then built a query[qryApptDis], that is supposed to look at only
the
records whose [apptdate] = [cboApptDate] on the form. This query is
what I used for the form's [SnglApptDis] RecordSource.
The value of [cboApptDate] is set by a calendar control.
This is the SQL for the query:

SELECT tblApptDis.CustomerID, tblApptDis.Row,
tblApptDis.RepairOrder,
tblApptDis.Name, tblApptDis.PhoneNumber, tblApptDis.Model,
tblApptDis.JobDescription, tblApptDis.FlatRate, tblApptDis.Tech,
tblApptDis.Status, tblApptDis.ApptDate, tblApptDis.Notify
FROM tblApptDis
WHERE (((tblApptDis.ApptDate)=[Forms]![SnglApptDis]![cboApptDate]));

When I open the form I don't see any records, it's opening as if it
were a
data entry form. (it's not). When I add a record it does go to the
table
correctly.
I'm sure I'm missing something obvious, can you see it?
Thanks again,
Paul

:

No offense, but if you've got multiple tables all alike, with names
like
5/13/2008, your design is wrong.

For one thing, you're hiding data in the names of the table.

Put everything into a single table with an additional date field in
it.
(Don't name the field Date: that's a reserved word!)

If you cannot (or will not) correct your design, try


Me.Text42.Value = DSum("[FlatRate]" , Me.cboApptDate.Value)

I don't see the need for variable FRSum (unless you're using the
value
elsewhere in the routine). If you do need FRSum, don't declare it
as
String.
DSum returns a number, not a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
The combo box value is set by a calendar control, Value = Date.
The table names are in a date format, ie; 5/13/2008.
I'm unsure of how to write the code for the DSum function.
Can you help?
Paul

:

I'm not sure I follow how a combo box named cboApptDate would
related
to
a
domain name, but yes, you can use a variable or a reference to a
function
that returns a string in all of the domain aggregate functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello, is it possible to set the domain argument of a DSum
expression
programmatticaly?

For example:
Dim FRSum As String
FRSum = DSum("[FlatRate]" , "(cboApptDate.Value) = domain
name")
Me.Text42.Value = FRSum
Or something like that, I haven't been able to find an example
and haven't been able to get anything to work.
Thanks in advance for any help.
Paul