conversion to Access 2003 giving errors

B

bubu

I have converted from Access97 to Access2003 for a client (they decided not
to go 2007).
When I run a specific report for them it gives the "Expression is typed
incorrectly, or is too complex to be evaluated. For example, a numeric
expression .." error.

This is something that worked previously. The query that causes this is:

SELECT DISTINCTROW dbo_inventory_balance_fact.inventory_balance_date_c,
dbo_inventory_balance_fact.entity_id AS entity, dbo_v_site_stockroom.site_id,
dbo_v_site_stockroom.site_descp, dbo_v_site_stockroom.stockroom_descp,
dbo_v_site_stockroom.stockroom_id,
ProductHierarchy.major_product_family_code,
ProductHierarchy.major_product_family_descp,
ProductHierarchy.item_family_code, ProductHierarchy.product_family_descp,
dbo_Product.metal_chemical_symbol, dbo_inventory_balance_fact.item_id,
ProductHierarchy.item_description,
dbo_inventory_balance_fact.on_hand_bill_wt_kgs,
dbo_inventory_balance_fact.on_hand_bill_wt_lbs,
dbo_inventory_balance_fact.in_transit_bill_wt_kgs,
dbo_inventory_balance_fact.in_transit_bill_wt_lbs,
CDate([dbo_v_time]![period]) AS period
FROM (((dbo_inventory_balance_fact LEFT JOIN dbo_v_site_stockroom ON
(dbo_inventory_balance_fact.stockroom_id = dbo_v_site_stockroom.stockroom_id)
AND (dbo_inventory_balance_fact.site_id = dbo_v_site_stockroom.site_id)) LEFT
JOIN dbo_v_time ON dbo_inventory_balance_fact.inventory_balance_date_c =
dbo_v_time.time_dim_date_c) LEFT JOIN ProductHierarchy ON
dbo_inventory_balance_fact.item_id = ProductHierarchy.item_id) LEFT JOIN
dbo_Product ON dbo_inventory_balance_fact.item_id = dbo_Product.item_id
WHERE (((CDate([dbo_v_time]![period]))>=CDate([Forms]![Criteria
Form]![cbxStartPeriod]) And
(CDate([dbo_v_time]![period]))<=CDate([Forms]![Criteria
Form]![cbxEndPeriod])));

Now I have tried to put the SELECT in another query and have another query
with the parameters and I get "Invalid use of NULL"

Here is the query1:

SELECT [qryInvent7351CloseBalALL-bu].inventory_balance_date_c,
[qryInvent7351CloseBalALL-bu].entity, [qryInvent7351CloseBalALL-bu].site_id,
[qryInvent7351CloseBalALL-bu].site_descp,
[qryInvent7351CloseBalALL-bu].stockroom_descp,
[qryInvent7351CloseBalALL-bu].stockroom_id,
[qryInvent7351CloseBalALL-bu].major_product_family_code,
[qryInvent7351CloseBalALL-bu].major_product_family_descp,
[qryInvent7351CloseBalALL-bu].item_family_code,
[qryInvent7351CloseBalALL-bu].product_family_descp,
[qryInvent7351CloseBalALL-bu].item_description,
[qryInvent7351CloseBalALL-bu].metal_chemical_symbol,
[qryInvent7351CloseBalALL-bu].item_id,
[qryInvent7351CloseBalALL-bu].on_hand_bill_wt_kgs,
[qryInvent7351CloseBalALL-bu].on_hand_bill_wt_lbs,
[qryInvent7351CloseBalALL-bu].in_transit_bill_wt_kgs,
[qryInvent7351CloseBalALL-bu].in_transit_bill_wt_lbs,
[qryInvent7351CloseBalALL-bu].period
FROM [qryInvent7351CloseBalALL-bu]
WHERE (period >= cdate('02/01/1999') ) And (period <= cdate('03/01/1999'));

NOTE: [qryInvent7351CloseBalALL-bu] is the original SELECT but without the
WHERE

Here I tried hard coding the parameters and it worked, but when I stretched
it to other dates it gives an error "Invalid use of NULL"

The tact that I was following is to create the query using VB code.
There was an error message when converting manually to Access 2003. Message
was in table "Conversion Errors" with 1 row saying "Module - There were
compilation errors during the conversion or enabling of this database." The
modules seem fine when I access them and save it.

Any other ideas or areas to look at? There are other reports seem to run
fine (for now).

Is it possible to have Access 2007 installed only, and have Office 2003 for
the rest?


Thanking everyone in advance.
 
J

Jerry Whittle

First I'd check for a problem with the CDate function. Do a Ctrl + g to bring
up the VBA window. Type in the following in the Immediate window and hit
Enter:

Debug.Print CDate("1/1/2009")

It should return 1/1/2009 without an error.

The next thing to check is the data or form references. CDate can't handle a
null value and will give you a 94 invalid use of null error just like you are
seeing. Try hardcoding in a date such as #6/6/2009# where you are referencing
a form field. See if that stops the error. Put in some proper dates and see
if the correct data returns. There may be a problem with Access seeing the
form fields especially if the cbs in cbxEndPeriod means combo box. You may
need to use the .value or .text property of the combo box.

http://msdn.microsoft.com/en-us/library/aa173476(office.11).aspx

Next CDate can't handle invalid dates like 13/13/2009. You may want to run a
query using the IsDate function of the columns in the tables like
[dbo_v_time]![period] to see if something snuck in that can't be evaluated as
a date.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


bubu said:
I have converted from Access97 to Access2003 for a client (they decided not
to go 2007).
When I run a specific report for them it gives the "Expression is typed
incorrectly, or is too complex to be evaluated. For example, a numeric
expression .." error.

This is something that worked previously. The query that causes this is:

SELECT DISTINCTROW dbo_inventory_balance_fact.inventory_balance_date_c,
dbo_inventory_balance_fact.entity_id AS entity, dbo_v_site_stockroom.site_id,
dbo_v_site_stockroom.site_descp, dbo_v_site_stockroom.stockroom_descp,
dbo_v_site_stockroom.stockroom_id,
ProductHierarchy.major_product_family_code,
ProductHierarchy.major_product_family_descp,
ProductHierarchy.item_family_code, ProductHierarchy.product_family_descp,
dbo_Product.metal_chemical_symbol, dbo_inventory_balance_fact.item_id,
ProductHierarchy.item_description,
dbo_inventory_balance_fact.on_hand_bill_wt_kgs,
dbo_inventory_balance_fact.on_hand_bill_wt_lbs,
dbo_inventory_balance_fact.in_transit_bill_wt_kgs,
dbo_inventory_balance_fact.in_transit_bill_wt_lbs,
CDate([dbo_v_time]![period]) AS period
FROM (((dbo_inventory_balance_fact LEFT JOIN dbo_v_site_stockroom ON
(dbo_inventory_balance_fact.stockroom_id = dbo_v_site_stockroom.stockroom_id)
AND (dbo_inventory_balance_fact.site_id = dbo_v_site_stockroom.site_id)) LEFT
JOIN dbo_v_time ON dbo_inventory_balance_fact.inventory_balance_date_c =
dbo_v_time.time_dim_date_c) LEFT JOIN ProductHierarchy ON
dbo_inventory_balance_fact.item_id = ProductHierarchy.item_id) LEFT JOIN
dbo_Product ON dbo_inventory_balance_fact.item_id = dbo_Product.item_id
WHERE (((CDate([dbo_v_time]![period]))>=CDate([Forms]![Criteria
Form]![cbxStartPeriod]) And
(CDate([dbo_v_time]![period]))<=CDate([Forms]![Criteria
Form]![cbxEndPeriod])));

Now I have tried to put the SELECT in another query and have another query
with the parameters and I get "Invalid use of NULL"

Here is the query1:

SELECT [qryInvent7351CloseBalALL-bu].inventory_balance_date_c,
[qryInvent7351CloseBalALL-bu].entity, [qryInvent7351CloseBalALL-bu].site_id,
[qryInvent7351CloseBalALL-bu].site_descp,
[qryInvent7351CloseBalALL-bu].stockroom_descp,
[qryInvent7351CloseBalALL-bu].stockroom_id,
[qryInvent7351CloseBalALL-bu].major_product_family_code,
[qryInvent7351CloseBalALL-bu].major_product_family_descp,
[qryInvent7351CloseBalALL-bu].item_family_code,
[qryInvent7351CloseBalALL-bu].product_family_descp,
[qryInvent7351CloseBalALL-bu].item_description,
[qryInvent7351CloseBalALL-bu].metal_chemical_symbol,
[qryInvent7351CloseBalALL-bu].item_id,
[qryInvent7351CloseBalALL-bu].on_hand_bill_wt_kgs,
[qryInvent7351CloseBalALL-bu].on_hand_bill_wt_lbs,
[qryInvent7351CloseBalALL-bu].in_transit_bill_wt_kgs,
[qryInvent7351CloseBalALL-bu].in_transit_bill_wt_lbs,
[qryInvent7351CloseBalALL-bu].period
FROM [qryInvent7351CloseBalALL-bu]
WHERE (period >= cdate('02/01/1999') ) And (period <= cdate('03/01/1999'));

NOTE: [qryInvent7351CloseBalALL-bu] is the original SELECT but without the
WHERE

Here I tried hard coding the parameters and it worked, but when I stretched
it to other dates it gives an error "Invalid use of NULL"

The tact that I was following is to create the query using VB code.
There was an error message when converting manually to Access 2003. Message
was in table "Conversion Errors" with 1 row saying "Module - There were
compilation errors during the conversion or enabling of this database." The
modules seem fine when I access them and save it.

Any other ideas or areas to look at? There are other reports seem to run
fine (for now).

Is it possible to have Access 2007 installed only, and have Office 2003 for
the rest?


Thanking everyone in advance.
Click to expand...
 
Top