Calculate Fieldname At Runtime
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.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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.
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
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.
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,
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.
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?
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.
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.
First field which will calculate the % of Q1
Second field which will calculate the % of Q2
the third field which will calculate the % of Q3
and the fourth field will calculate the % of Q4
AND the last field (the fifth one) should be the sum of Q1, Q2, Q3 and Q4 percentages and which should be equal to 100%