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
*

 

[AccessVandal via AccessMonster.com]

Hi Alberto,

I’m just curious, what Access version and OS? It might help to know.

 

[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?

 

[AccessVandal via AccessMonster.com]

Same here Alberto.

It appears that these problems is with A2007 and Vista. I do have problems
with A2007 that can't read the value of a control. It appears fine in
debugging, the value is correct.

It appears yours is more complicated than mine. I managed to do a minor
recoding on this error to clear up my problem. Since than, even the backup
older version, I was unable to replicate the same error.

In XP, the problem was due to windows updates not Office update. I'm using
A2K7 Runtime with XP and A2K7 Runtime with Vista Bus. What I did was just
recompile, compact and repair. Had no problems from than on. No code changes
at all and these apps are in A2K format.

Hopefully, we can get more post on A2007 to confirm of these errors.

 

[AccessVandal via AccessMonster.com]

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");