Macro Problem

[Lesley]

Hi there

I'm not sure if this is in the correct forum. I couldn't see a Macros forum.



I created a database some years ago now which was designed using Office (very old) possibly 93/97

It has run well for may years just requiring a little tweaking every now an then as needs changed.

The database only contains two macros which are used to run queries requiring data to be selected which falls between dates selected through the macro, one macro for invoices and one for statistics.

I'm now doing an overhaul of the database to run it in Office 2013 and find that the macros give an error message:

"The Expression you entered has a function name that Microsoft Access can't find".

The folder containing the database is set as a Trusted Location. There are yellow warning triangles beside 3 of the items in the macros.

I'm stumped ! Can it be fixed ? Or is there an easier way to select a range of dates to include in a query ?

PS would using .mdb or .accdb make any difference ?

[ItsMe]

The likely problem is that the macro is not converting from one version to the next. You are skipping many versions of office.

Perhaps building a query in combination of a form or report will be the best solution. My preference is VBA. You are more likely to get help with VBA and objects like forms and queries. It is just easier to help..

Here is an example. You might try searching for SQL, Between, Date
https://www.accessforums.net/queries...vba-15704.html

[lfpm062010]

It might be missing library reference.

Viewing Access Database References for both old and the new. Make sure the new matches the old. If you have question on which one to use. Just list them out. We can help you to decide.

To view the current database references:

1. Open the database.
2. Press ALT+F11 to start Visual Basic Editor.
3. On the Tools menu, click References.

[Lesley]

The likely problem is that the macro is not converting from one version to the next. You are skipping many versions of office.

Perhaps building a query in combination of a form or report will be the best solution. My preference is VBA. You are more likely to get help with VBA and objects like forms and queries. It is just easier to help..

Here is an example. You might try searching for SQL, Between, Date
https://www.accessforums.net/queries...vba-15704.html

I have never used VBA so my learning curve would be steeeep ! But I'll have a look at that link, many thanks!

[Lesley]

It might be missing library reference.

Viewing Access Database References for both old and the new. Make sure the new matches the old. If you have question on which one to use. Just list them out. We can help you to decide.

To view the current database references:

1. Open the database.
2. Press ALT+F11 to start Visual Basic Editor.
3. On the Tools menu, click References.

Many thanks. I'll have a look at the references.

Tomorrow I'll be at the Office where the old version is running and will check what the old macros are like. The Office version there is 97 I think.

I have 2013 on my computers and will compare it to that.

[Lesley]

The likely problem is that the macro is not converting from one version to the next. You are skipping many versions of office.

Perhaps building a query in combination of a form or report will be the best solution. My preference is VBA. You are more likely to get help with VBA and objects like forms and queries. It is just easier to help..

Here is an example. You might try searching for SQL, Between, Date

Yes skipping from 97? to 2013.

I've never used Visual basic so am a bit out of my depth here.

I'll have a look at the link you've given, Many thanks.

[Lesley]

I'm still not sure if I should be using an .mdb or an .accbd file type ?

[ItsMe]

If you use lfpm062010's advice, you might be able to locate "Missing" references in the 2013 version. In the References window it should list libraries that are missing. Of course, you may still want to take inventory of references in the original version.

If you find a missing reference you could look for a similar library in the new version and select it.

[ItsMe]

I'm still not sure if I should be using an .mdb or an .accbd file type ?

Hard to say. I would keep it .mdb for now and try the conversion with a copy at a later date.

[Lesley]

Good advice. Change one thing at a time.

[Lesley]

I'll be checking to see if there are any missing references tomorrow when I go to the office.


Does this look ok ? :

Between [Forms]![WBS set date for Invoice]![BeginningDate] And [Forms]![WBS set date for Invoice]![EndDate]

It is the criteria in the query that refers to the macro that selects the start and end dates.

[ItsMe]

I don't normally reference forms in queries because it limits the use/functionality of the query. But that does look correct at a glance. Did you try it in a query?

You might want to add some additional criteria to check for null in your date value. A simple way would be to add a column as an alias to your date field. There probably is a way you can get it all in a single line of criteria. I am just not imagining it right now.

[June7]

Two issues with searches:

1. field might be Null, records with Null will not be retrieved which might or might not be acceptable
If this is a possibility with your data, then as ItsMe suggested, create a field with expression that converts the Null and apply criteria to that field
SearchDate: Nz([fieldname], Date())

2. the input might not be provided
Between Nz([Forms]![WBS set date for Invoice]![BeginningDate],#1/1/1900#) And Nz([Forms]![WBS set date for Invoice]![EndDate],#12/31/2099#)

I also don't use dynamic parameterized queries. I prefer to pass filter criteria to the form or report through the WHERE CONDITION argument of OpenForm (or OpenReport). This can be done in macro or VBA.

Users really should not interact directly with tables and queries, just forms and reports.

I am inclined to think you won't solve this macro issue until you convert the db to 2013 accdb and rebuild the macro.

Post the macro items that have the error or provide db for analysis.