Topics Search

Calculate Fieldname At Runtime

Calculate Fieldname At Runtime
Views: 3
Can I calculate a fieldname at runtime for use in a query so that I don't have to rewrite the query at each change of fiscal year.

I have fields named "9-10", "10-11", "11-12", . In my queries, I identify the fields as tblName.[9-10], tblName.[10-11], tblName.[11-12], etc.

I have a function -- strCurrFiscalYr -- which returns a string representing the current fiscal year, e.g. "11-12". I would like to use the function to generate the fieldname at runtime for use in a query so that I don't have to rewrite the queries annually. Is this possible?

I'm aware that the data structure is severely flawed, but have to live with it.
Sponsored Links:

More topics


Open a form from form

I am opening form B with a button on form A. Form B opens to a record with matching field on form A. If the form Bs source doesnt have the matching value, then I would like to go to a new record on form B and insert the value. I used the standard open B where B.fieldName =A.fieldName etc. to open form B.
If B.fieldName doesnt exist, the B form usually opens to a new record which I detect with if isnull(B.fieldname), then B.fieldname=A.fieldname. It is not consistent. Sometimes I get an error message and Form B opens with errors that indicate the source query is not correct (#NAME everywhere) and there is no value in B.fieldname Is this instability my computer or is it caused by lmy procedure? I am using A2007 and W7.

DateTime calculations

I am having an issue summing/formatting a time value and hope you can help.

I have a manually edited field which contains a long-time value xx:xx:xx. I need to calculate the total of all the values in this field but, of course, hit the 23:59 barrier and get rolled over to 00:00 if I leave it in long-time format .

If I use "=Sum([FieldName])" then I get 1.29166666666667 as a total of 00:10:00 + 10:20:00 + 20:30:00 whereas it should read 31:00:00. I am assuming this equals 1.29 days but (without resorting to functions like dur2sec) what is the syntax required to get it to display as 31:00:00?

I have tried using

=Format([FieldName] Mod 24,"\:00")

as I can't use DateDiff due to not having a start- and end date to perform a calculation

VB Code using variables for Forms controls

Like to put the form property name in a variable. I have a table that controls form element. Hols formname, fieldname, Propertyname and the Value I want the property set to.

Formnname = "test_form"
FieldName = "command0"
Propertyname = "Visible"
Forms(Formname).Form(FieldName).Propertyname = False

Can not seem to figure out if you can put the property name as a variable like the form name and fieldname.

Date conversion from YYYYMMDD to DD/MM/YYYY

I am trying to convert the date in a table (table name - 2009-2011 event records) with the field name 'Date of registration' with the data type numer and layout YYYYMMDD, into DD/MM/YYYY, does anyone know the code to do this please? I have tried other codes off the internet such as:

Format(DateSerial (Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)), "dd/mm/yyyy")

But access isn't allowing me to start the query with Format,

Cannot pass date from form to query

I have a form created to set parameters for a query. A few of the fields have the standard method, using.


in the Query criteria, and they are working correctly. Three fields in the Query have the Fields defined by a value entered in the Form, using.

FieldName: Forms![FormEntry]![FieldName]

In the Query Field. Two of them are working correctly, and one is not.

The problematic field is a combo box that works correctly when it contains and passes text, but when dates are used nothing is passed.

Is there some special format I need to use in the Forms![FormEntry]![FieldName] string to pass a date field format to a query?

Needing more info about using Access runtime vs. ACCDE

I'm trying to understand Access Runtime, as compared to an ACCDE file.

1) If I use Runtime, does it eliminate the need to hide the navigation pane, menus and ribbons?
2) Does it create an executable file that can be used on any computer, regardless of whether Access is installed or not?
3) Does Runtime need to be previously installed on the Users' computers?
4) Is a Runtime file a smaller footprint than an ACCDE?
5) Are there disadvantages to distributing a Runtime file instead of an ACCDE?

The sources I've found so far on Runtime are a bit confusing.

Access Runtime

Access 2007 & 2010 runtime. Has anyone used the runtime versions? I have an access report that uses a csv file for data. It works fine under the full version but the runtime does not open it

difference between [field] and field

I understand that in an SQL string, you could either use [fieldName] or fieldName to search for an field in select statement, may I know what are the difference between the

If Statement

If I put an Iff statement into a Query such as:

IIf([Table A]![FieldName] Like "A" Or [Table A]![FieldName] Like "B","Answer A", "Answer B")

It works ok but as the table name is the same why do I have to repeat it. If I try:

IIf([Table A]![FieldName] Like "A" Or Like "B","Answer A", "Answer B")

It does not seem to evaluate correctly.

This means that multiple "Or" criteria referencing the same field results in very long queries. I know you dont need to in VBA but I am doing this in a query grid.

Acccess 2007 Runtime and Queries

While I've created MDE files before, I've never used the Runtime for Access. I was wondering if the runtime limits you to only running existing queries, or can you modify them using the runtime too?