Run-time Error 2186

[Alberto]

Everytime I open up change a form from the "Design" to the "View" format, I
get the following error:
Run-time Error: 2186
This property isn't available in Design view

I believe the error stems from the fact that the form has several buttons on
it that open queries that reference a two date parameter fields and
manufacturer parameter fields that filter the query when it is run. I know
this because when I take those buttons off of the form, the error disappears.

When I obtain the "Run-time Error 2186" and hit debug, the following is
highlighted:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function

or

Public Function Manufacturer() As String
If Not Form_StartForm.ManufacturersID.Value Then
Manufacturer = Form_StartForm.ManufacturersID.Value
End If
End Function


Also, sometimes the run-time error appears as a 2467 error. THis occurs the
first time that I open the form.

I am fairly green with MSFT Access so I don't even know where to begin.
What should I do to solve this?

 

[strive4peace]

Hi Alberto,

forms!Form_StartForm!DateFrom.Value
or
forms!Form_StartForm.DateFrom.value

since Value is the default property, you can also leave .Value off the end


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Alberto]

Thanks. I tried what you suggested and I changed it from:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function


to:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(forms!Form_StartForm!DateFrom.Value, 2)
End Function


I now get:

Run-time error 2450.
Can't find the form 'l' referred to in a macro expression or Visual Basic
code.

 

[Douglas J. Steele]

Assuming your form is actually named StartForm, use
Forms!StartForm!DateFrom.

Form_StartForm is actually how you refer to the form's Class Module.

 

[Alberto]

I should also point out that there are other buttons that also reference the
DateFrom and DateTo fields on the form that work well and do not create these
2467 or 2186 errors.

 

[strive4peace]

good catch, Doug

Warm Regards,
Crystal

*
have an awesome day
*

 

[strive4peace]

Hi Alberto,

if you are in the code behind your form, you can use this reference form:

Me.controlname

ie:

Me.DateTo

the nice thing about using Me. is that, once you type it into the code,
Access shows you options you can pick <smile>


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Alberto]

I changed it to:
Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Forms!StartForm!DateFrom.Value, 2)
End Function

and still get the errors.

I am at a loss because there are other buttons that are not generating this
error even though they rely on the same DateFrom and DateTo paramters.

 

[strive4peace]

Hi Alberto,

if you are returning the value as a date data type, do not format it as
that converts to a string. What is the code for FormatDateTime?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Alberto]

I'm not following when you say "what is the code for FormatDateTime?" Can
you expand? I'm a bit of an Access neophyte.

Also, another bit of information. The queries that appear to be giving me
the most problems are crosstab queries where the columns are weeks

Wk1 Wk2 Wk3 Wk4
# of builds

The date function within the crosstab query I'm using to create the weekly
total is:
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

Alberto

 

[strive4peace]

Hi Alberto,

I see that FormatDateTime is a built-in Access function -- sorry, I did
not realize that as I have never used it ... strike my remark about
asking you for code.

"and still get the errors"

what is the error message that you are getting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I'm not following when you say "what is the code for FormatDateTime?" Can
you expand? I'm a bit of an Access neophyte.

Also, another bit of information. The queries that appear to be giving me
the most problems are crosstab queries where the columns are weeks

Wk1 Wk2 Wk3 Wk4
# of builds

The date function within the crosstab query I'm using to create the weekly
total is:
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

Alberto

Hi Alberto,

if you are returning the value as a date data type, do not format it as
that converts to a string. What is the code for FormatDateTime?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Alberto]

Run-time error '2186':
This property isn't available in Design view.

Interestingly, when I have the button open the crosstab query entitled
CrosstabNumberOfBuilds, I get this error. But when I have the button
reference the query on which the Crosstab query is based, entitled
qryNumberOfBuilds, I do not get the error.

Very odd.

Hi Alberto,

I see that FormatDateTime is a built-in Access function -- sorry, I did
not realize that as I have never used it ... strike my remark about
asking you for code.

"and still get the errors"

what is the error message that you are getting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I'm not following when you say "what is the code for FormatDateTime?" Can
you expand? I'm a bit of an Access neophyte.

Also, another bit of information. The queries that appear to be giving me
the most problems are crosstab queries where the columns are weeks

Wk1 Wk2 Wk3 Wk4
# of builds

The date function within the crosstab query I'm using to create the weekly
total is:
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

Alberto

Hi Alberto,

if you are returning the value as a date data type, do not format it as
that converts to a string. What is the code for FormatDateTime?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I changed it to:
Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Forms!StartForm!DateFrom.Value, 2)
End Function

and still get the errors.

I am at a loss because there are other buttons that are not generating this
error even though they rely on the same DateFrom and DateTo paramters.


:

Assuming your form is actually named StartForm, use
Forms!StartForm!DateFrom.

Form_StartForm is actually how you refer to the form's Class Module.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. I tried what you suggested and I changed it from:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function


to:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(forms!Form_StartForm!DateFrom.Value, 2)
End Function


I now get:

Run-time error 2450.
Can't find the form 'l' referred to in a macro expression or Visual Basic
code.



:

Hi Alberto,

forms!Form_StartForm!DateFrom.Value
or
forms!Form_StartForm.DateFrom.value

since Value is the default property, you can also leave .Value off the
end


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
Everytime I open up change a form from the "Design" to the "View"
format, I
get the following error:
Run-time Error: 2186
This property isn't available in Design view

I believe the error stems from the fact that the form has several
buttons on
it that open queries that reference a two date parameter fields and
manufacturer parameter fields that filter the query when it is run. I
know
this because when I take those buttons off of the form, the error
disappears.

When I obtain the "Run-time Error 2186" and hit debug, the following is
highlighted:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function

or

Public Function Manufacturer() As String
If Not Form_StartForm.ManufacturersID.Value Then
Manufacturer = Form_StartForm.ManufacturersID.Value
End If
End Function


Also, sometimes the run-time error appears as a 2467 error. THis
occurs the
first time that I open the form.

I am fairly green with MSFT Access so I don't even know where to begin.
What should I do to solve this?

 

[Alberto]

I'm running Access 2007 with Vista Business.

Interestingly, when I have the button open the crosstab query entitled
CrosstabNumberOfBuilds, I get this error. But when I have the button
reference the query on which the Crosstab query is based, entitled
qryNumberOfBuilds, I do not get the error.

Very odd.

 

[strive4peace]

Hi Alberto,

Crosstabs can be tricky creatures -- if you found a way that works, run
with it!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Run-time error '2186':
This property isn't available in Design view.

Interestingly, when I have the button open the crosstab query entitled
CrosstabNumberOfBuilds, I get this error. But when I have the button
reference the query on which the Crosstab query is based, entitled
qryNumberOfBuilds, I do not get the error.

Very odd.

Hi Alberto,

I see that FormatDateTime is a built-in Access function -- sorry, I did
not realize that as I have never used it ... strike my remark about
asking you for code.

"and still get the errors"

what is the error message that you are getting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I'm not following when you say "what is the code for FormatDateTime?" Can
you expand? I'm a bit of an Access neophyte.

Also, another bit of information. The queries that appear to be giving me
the most problems are crosstab queries where the columns are weeks

Wk1 Wk2 Wk3 Wk4
# of builds

The date function within the crosstab query I'm using to create the weekly
total is:
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

Alberto



:

Hi Alberto,

if you are returning the value as a date data type, do not format it as
that converts to a string. What is the code for FormatDateTime?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I changed it to:
Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Forms!StartForm!DateFrom.Value, 2)
End Function

and still get the errors.

I am at a loss because there are other buttons that are not generating this
error even though they rely on the same DateFrom and DateTo paramters.


:

Assuming your form is actually named StartForm, use
Forms!StartForm!DateFrom.

Form_StartForm is actually how you refer to the form's Class Module.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. I tried what you suggested and I changed it from:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function


to:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(forms!Form_StartForm!DateFrom.Value, 2)
End Function


I now get:

Run-time error 2450.
Can't find the form 'l' referred to in a macro expression or Visual Basic
code.



:

Hi Alberto,

forms!Form_StartForm!DateFrom.Value
or
forms!Form_StartForm.DateFrom.value

since Value is the default property, you can also leave .Value off the
end


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
Everytime I open up change a form from the "Design" to the "View"
format, I
get the following error:
Run-time Error: 2186
This property isn't available in Design view

I believe the error stems from the fact that the form has several
buttons on
it that open queries that reference a two date parameter fields and
manufacturer parameter fields that filter the query when it is run. I
know
this because when I take those buttons off of the form, the error
disappears.

When I obtain the "Run-time Error 2186" and hit debug, the following is
highlighted:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function

or

Public Function Manufacturer() As String
If Not Form_StartForm.ManufacturersID.Value Then
Manufacturer = Form_StartForm.ManufacturersID.Value
End If
End Function


Also, sometimes the run-time error appears as a 2467 error. THis
occurs the
first time that I open the form.

I am fairly green with MSFT Access so I don't even know where to begin.
What should I do to solve this?

 

[Alberto]

"if you found a way that works, run with it!"
I haven't really. Right now the underlying query, qryNumberOfBuilds, runs
vertically and I need it to run horizontally so that it is similar to all of
the other queries and reports that individuals run.

Hi Alberto,

Crosstabs can be tricky creatures -- if you found a way that works, run
with it!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Run-time error '2186':
This property isn't available in Design view.

Interestingly, when I have the button open the crosstab query entitled
CrosstabNumberOfBuilds, I get this error. But when I have the button
reference the query on which the Crosstab query is based, entitled
qryNumberOfBuilds, I do not get the error.

Very odd.

Hi Alberto,

I see that FormatDateTime is a built-in Access function -- sorry, I did
not realize that as I have never used it ... strike my remark about
asking you for code.

"and still get the errors"

what is the error message that you are getting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I'm not following when you say "what is the code for FormatDateTime?" Can
you expand? I'm a bit of an Access neophyte.

Also, another bit of information. The queries that appear to be giving me
the most problems are crosstab queries where the columns are weeks

Wk1 Wk2 Wk3 Wk4
# of builds

The date function within the crosstab query I'm using to create the weekly
total is:
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

Alberto



:

Hi Alberto,

if you are returning the value as a date data type, do not format it as
that converts to a string. What is the code for FormatDateTime?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I changed it to:
Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Forms!StartForm!DateFrom.Value, 2)
End Function

and still get the errors.

I am at a loss because there are other buttons that are not generating this
error even though they rely on the same DateFrom and DateTo paramters.


:

Assuming your form is actually named StartForm, use
Forms!StartForm!DateFrom.

Form_StartForm is actually how you refer to the form's Class Module.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. I tried what you suggested and I changed it from:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function


to:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(forms!Form_StartForm!DateFrom.Value, 2)
End Function


I now get:

Run-time error 2450.
Can't find the form 'l' referred to in a macro expression or Visual Basic
code.



:

Hi Alberto,

forms!Form_StartForm!DateFrom.Value
or
forms!Form_StartForm.DateFrom.value

since Value is the default property, you can also leave .Value off the
end


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
Everytime I open up change a form from the "Design" to the "View"
format, I
get the following error:
Run-time Error: 2186
This property isn't available in Design view

I believe the error stems from the fact that the form has several
buttons on
it that open queries that reference a two date parameter fields and
manufacturer parameter fields that filter the query when it is run. I
know
this because when I take those buttons off of the form, the error
disappears.

When I obtain the "Run-time Error 2186" and hit debug, the following is
highlighted:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function

or

Public Function Manufacturer() As String
If Not Form_StartForm.ManufacturersID.Value Then
Manufacturer = Form_StartForm.ManufacturersID.Value
End If
End Function


Also, sometimes the run-time error appears as a 2467 error. THis
occurs the
first time that I open the form.

I am fairly green with MSFT Access so I don't even know where to begin.
What should I do to solve this?

 

[strive4peace]

Hi Alberto,

please give us an example of how you want the data to look and the
equations (or logic) that would need to be used to generate what you
want to show

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

"if you found a way that works, run with it!"
I haven't really. Right now the underlying query, qryNumberOfBuilds, runs
vertically and I need it to run horizontally so that it is similar to all of
the other queries and reports that individuals run.

Hi Alberto,

Crosstabs can be tricky creatures -- if you found a way that works, run
with it!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Run-time error '2186':
This property isn't available in Design view.

Interestingly, when I have the button open the crosstab query entitled
CrosstabNumberOfBuilds, I get this error. But when I have the button
reference the query on which the Crosstab query is based, entitled
qryNumberOfBuilds, I do not get the error.

Very odd.

:

Hi Alberto,

I see that FormatDateTime is a built-in Access function -- sorry, I did
not realize that as I have never used it ... strike my remark about
asking you for code.

"and still get the errors"

what is the error message that you are getting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I'm not following when you say "what is the code for FormatDateTime?" Can
you expand? I'm a bit of an Access neophyte.

Also, another bit of information. The queries that appear to be giving me
the most problems are crosstab queries where the columns are weeks

Wk1 Wk2 Wk3 Wk4
# of builds

The date function within the crosstab query I'm using to create the weekly
total is:
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

Alberto



:

Hi Alberto,

if you are returning the value as a date data type, do not format it as
that converts to a string. What is the code for FormatDateTime?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I changed it to:
Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Forms!StartForm!DateFrom.Value, 2)
End Function

and still get the errors.

I am at a loss because there are other buttons that are not generating this
error even though they rely on the same DateFrom and DateTo paramters.


:

Assuming your form is actually named StartForm, use
Forms!StartForm!DateFrom.

Form_StartForm is actually how you refer to the form's Class Module.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. I tried what you suggested and I changed it from:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function


to:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(forms!Form_StartForm!DateFrom.Value, 2)
End Function


I now get:

Run-time error 2450.
Can't find the form 'l' referred to in a macro expression or Visual Basic
code.



:

Hi Alberto,

forms!Form_StartForm!DateFrom.Value
or
forms!Form_StartForm.DateFrom.value

since Value is the default property, you can also leave .Value off the
end


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
Everytime I open up change a form from the "Design" to the "View"
format, I
get the following error:
Run-time Error: 2186
This property isn't available in Design view

I believe the error stems from the fact that the form has several
buttons on
it that open queries that reference a two date parameter fields and
manufacturer parameter fields that filter the query when it is run. I
know
this because when I take those buttons off of the form, the error
disappears.

When I obtain the "Run-time Error 2186" and hit debug, the following is
highlighted:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function

or

Public Function Manufacturer() As String
If Not Form_StartForm.ManufacturersID.Value Then
Manufacturer = Form_StartForm.ManufacturersID.Value
End If
End Function


Also, sometimes the run-time error appears as a 2467 error. THis
occurs the
first time that I open the form.

I am fairly green with MSFT Access so I don't even know where to begin.
What should I do to solve this?

 

[Alberto]

Crystal,

Thanks for all of your help.

Currently, qryNumberOfBuilds looks like this:
Week SumOfproducts_quantity
1/1/07 100
1/8/07 123
1/15/07 130


I need it to look like this:
1/1/07 1/8/07 1/15/07
SumOfproducts_quantity 100 123 130

Here is the underlying SQL.
SELECT Sum(orders_products.products_quantity) AS SumOfproducts_quantity,
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd") AS Week
FROM orders_status INNER JOIN (((orders INNER JOIN
orders_products_attributes ON
orders.orders_id=orders_products_attributes.orders_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=orders_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_status
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_values) Like "*Build*") AND
((orders_products_attributes.products_options) Like "*Build*") AND
((orders_total.class)="ot_total") AND
((orders_status.orders_status_name)="Shipped"))
GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

Hi Alberto,

please give us an example of how you want the data to look and the
equations (or logic) that would need to be used to generate what you
want to show

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

"if you found a way that works, run with it!"
I haven't really. Right now the underlying query, qryNumberOfBuilds, runs
vertically and I need it to run horizontally so that it is similar to all of
the other queries and reports that individuals run.

Hi Alberto,

Crosstabs can be tricky creatures -- if you found a way that works, run
with it!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
Run-time error '2186':
This property isn't available in Design view.

Interestingly, when I have the button open the crosstab query entitled
CrosstabNumberOfBuilds, I get this error. But when I have the button
reference the query on which the Crosstab query is based, entitled
qryNumberOfBuilds, I do not get the error.

Very odd.

:

Hi Alberto,

I see that FormatDateTime is a built-in Access function -- sorry, I did
not realize that as I have never used it ... strike my remark about
asking you for code.

"and still get the errors"

what is the error message that you are getting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I'm not following when you say "what is the code for FormatDateTime?" Can
you expand? I'm a bit of an Access neophyte.

Also, another bit of information. The queries that appear to be giving me
the most problems are crosstab queries where the columns are weeks

Wk1 Wk2 Wk3 Wk4
# of builds

The date function within the crosstab query I'm using to create the weekly
total is:
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

Alberto



:

Hi Alberto,

if you are returning the value as a date data type, do not format it as
that converts to a string. What is the code for FormatDateTime?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
I changed it to:
Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Forms!StartForm!DateFrom.Value, 2)
End Function

and still get the errors.

I am at a loss because there are other buttons that are not generating this
error even though they rely on the same DateFrom and DateTo paramters.


:

Assuming your form is actually named StartForm, use
Forms!StartForm!DateFrom.

Form_StartForm is actually how you refer to the form's Class Module.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. I tried what you suggested and I changed it from:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function


to:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(forms!Form_StartForm!DateFrom.Value, 2)
End Function


I now get:

Run-time error 2450.
Can't find the form 'l' referred to in a macro expression or Visual Basic
code.



:

Hi Alberto,

forms!Form_StartForm!DateFrom.Value
or
forms!Form_StartForm.DateFrom.value

since Value is the default property, you can also leave .Value off the
end


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Alberto wrote:
Everytime I open up change a form from the "Design" to the "View"
format, I
get the following error:
Run-time Error: 2186
This property isn't available in Design view

I believe the error stems from the fact that the form has several
buttons on
it that open queries that reference a two date parameter fields and
manufacturer parameter fields that filter the query when it is run. I
know
this because when I take those buttons off of the form, the error
disappears.

When I obtain the "Run-time Error 2186" and hit debug, the following is
highlighted:

Public Function FromDate() As Date
' Get FROM date from DatePicker
FromDate = FormatDateTime(Form_StartForm.DateFrom.Value, 2)
End Function

or

Public Function Manufacturer() As String
If Not Form_StartForm.ManufacturersID.Value Then
Manufacturer = Form_StartForm.ManufacturersID.Value
End If
End Function


Also, sometimes the run-time error appears as a 2467 error. THis
occurs the
first time that I open the form.

I am fairly green with MSFT Access so I don't even know where to begin.
What should I do to solve this?

 

[Alberto]

Thanks for your responses. Here are the answers to your questions:

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

I can't say much about this. Have you test this alone on the separated query?

This works fine on the separated query. It is the method by which I'm
grouping [products_quantity] on a weekly basis.

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

Should it be "GROUP BY Week;"?

I created the query in "Design" view within Access and this is the way the
SQL came out. When I went into the SQL to change it to "GROUP BY Week" I got
an error of the following nature:
"You tried to execute a query that does not include the specified expression
'Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")' as part
of an aggregate function.

I also tried recompiling,

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

I can't say much about this. Have you test this alone on the separated query?

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

Should it be "GROUP BY Week;"?

Is Week reserved in a subroutine or function?

Crystal,

Thanks for all of your help.

Currently, qryNumberOfBuilds looks like this:
Week SumOfproducts_quantity
1/1/07 100
1/8/07 123
1/15/07 130

I need it to look like this:
1/1/07 1/8/07 1/15/07
SumOfproducts_quantity 100 123 130

Here is the underlying SQL.
SELECT Sum(orders_products.products_quantity) AS SumOfproducts_quantity,
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd") AS Week
FROM orders_status INNER JOIN (((orders INNER JOIN
orders_products_attributes ON
orders.orders_id=orders_products_attributes.orders_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=orders_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_status
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_values) Like "*Build*") AND
((orders_products_attributes.products_options) Like "*Build*") AND
((orders_total.class)="ot_total") AND
((orders_status.orders_status_name)="Shipped"))
GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

 

[Alberto]

It turns out that I had not set the data types of all dynamic criteria in the
Query->Parameters. I needed to do this for FromDate() and ToDate().

Once I did that, it seemed to fix the issue. Thanks for all of your help.

Alberto

Thanks for your responses. Here are the answers to your questions:

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

I can't say much about this. Have you test this alone on the separated query?

This works fine on the separated query. It is the method by which I'm
grouping [products_quantity] on a weekly basis.

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

Should it be "GROUP BY Week;"?

I created the query in "Design" view within Access and this is the way the
SQL came out. When I went into the SQL to change it to "GROUP BY Week" I got
an error of the following nature:
"You tried to execute a query that does not include the specified expression
'Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")' as part
of an aggregate function.

I also tried recompiling,

Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd")

I can't say much about this. Have you test this alone on the separated query?

GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

Should it be "GROUP BY Week;"?

Is Week reserved in a subroutine or function?

Crystal,

Thanks for all of your help.

Currently, qryNumberOfBuilds looks like this:
Week SumOfproducts_quantity
1/1/07 100
1/8/07 123
1/15/07 130

I need it to look like this:
1/1/07 1/8/07 1/15/07
SumOfproducts_quantity 100 123 130

Here is the underlying SQL.
SELECT Sum(orders_products.products_quantity) AS SumOfproducts_quantity,
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd") AS Week
FROM orders_status INNER JOIN (((orders INNER JOIN
orders_products_attributes ON
orders.orders_id=orders_products_attributes.orders_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=orders_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_status
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_values) Like "*Build*") AND
((orders_products_attributes.products_options) Like "*Build*") AND
((orders_total.class)="ot_total") AND
((orders_status.orders_status_name)="Shipped"))
GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");

 

[strive4peace]

glad you got it working, Alberto!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*