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

Preview
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

Preview
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

Preview
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

Preview
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

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

Forms![FormEntry]![FieldName]

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

Preview
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

Preview
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

Preview
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

Preview
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

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