Related Topics

Calculate Fieldname At Runtime

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.

Calculating percentage (%)
I am wondering if it is realistic to create a query that will calculate the total % of a field based on four other field.


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%

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?

Database Runtime Question
I am designing a database in Access 2003. It is almost complete other then the fact I need to create a runtime for it to enable PCs without Access to
view the database. when the runtime is created and installed on the PCs that need it, if I need to add or make changes to the database will the users with the runtime program be able to see these changes or would
I need to create a new runtime for them to see the changes?

Once they have the runtime installed, updates are simply a matter of giving them updated Access files (MDB/MDE). In that sense it is no different than using regular Access.

A simply one, I hope.

If I use Format([TableName.FieldName],"v0") in a query, I get the results I want: v1, v2, v3, etc. But if I use Format([TableName.FieldName],"s0"), I get this: 01, 02, 03, etc. How do I get s1, s2, s3, etc. instead

update a table
You have been very helpful with my previous issue, I am trying you again.

I want to update a main table if a secondary table has a particular value. I am assuming you have to write every field in a table when data is being updated; that you cannot just send one field a new value. If this is true, is there an easy way to pull the matching record from the main table and immediately update from the secondary table?

Such as sql1 = SELECT * FROM myTable WHERE aValue = keyValue
then turn around and sql2 = UPDATE myTable SET field1 = !fieldname, & field2 = !field2, etc,.

This code did not like the !fieldname or recordset!fieldname. So, do I have to read from the main table, put the values into variables then update main table using the variable names?

Even if it is telling me to limber up my fingers and start typing

Recordset Fieldname Manipulation
I use code like the following to create virtual recordsets in my modules that allows me to manipulate data very easily and make my database very versatile.
Set BOMdb = DBEngine.Workspaces(0).Databases(0)
Set Monthset = BOMdb.OpenRecordset("tblMonths", DB_OPEN_TABLE)

Do While Not Monthset.EOF
varMonthName = Monthset![MonthName]


However what I really would find useful is to be able to specify the fieldname (shown in red) from a variable.

I have tried replacing the fieldname with a variable in the usual format as below but it never works

varFieldname = "November"

varMonthName = Monthset![" & varFieldname & "]

Anybody have any suggestions on how I can do this as it would save a lot of lines of

Changes in costs: table structure?
I am currently thinking about simplifying a database constructed by a prior employee.

It has basically the cost of service provisions by doctor surgeries (practice). The tables I have are as follows:







Each query can have many fields
Each field can have many records in the factbase table
Each practice can also have many records in the factbase table

The complication is that each record in the factbase table has a total number of patients which have an associated cost. This cost can change at any time.

I am not sure whether to have an M-2-M relationship because each fieldname will have potentially many associated costs but a cost would not be associated with many fields.

Should I create an M-2-M relationship and how would I achieve this

"FieldName" is not an index in this table. Error 3800
This is the code I'm using, error line highlighted:

Option Compare Database Public Function VerifyAcct(AcctNum As String) Dim dbDatabase As Database Dim rsAcctNumber As Recordset Set dbDatabase = CurrentDb Set rsAcctNumber = dbDatabase.OpenRecordset("CAM_Portfolio_Query", dbOpenTable) With rsAcctNumber
.Index = "Account-Number"
.Seek "=", AcctNum
If .NoMatch = True Then
MsgBox "NotFound"
MsgBox "Found"
End If End With
End Function

It is giving me Runtime 3800, "FieldName" is not an index in this table.
In the table CAM_Portfolio_Query, I set the [Account-Number] field to be both the key and indexed, with no duplicates allowed.

Yet I still get this error

How do I open access in runtime without security warnings?
I am in the process of upgrading from Access 2003 to 2010 the current databases are split and users run the application in runtime.
I have been able to upgrade successfully except for the Security Warning I get when opening the database in runtime.
Have tried changing settings in the Trust Centre, this has fixed the Full Access security but seems to have no effect on Runtime.

2010 Runtime ADODB Connection Failure
I have a routine which connects to MySQL database with ADODB connection. This worked in 2003 and 2007 runtime but throws a 430 error on 2010 runtime. It works in full access 2010. Has there been a change of policy to restrict this type of connection in the 2010 runtime?

If I use a linked ODBC table instead of ADODB the runtime fails with "Runtime Error". It still works in full access

Runtime access 2010
I need a beginners tutorial on the use of Runtime - I have access 2010 installed - I think I downloaded and installed access runtime for 2010 - for the life of me I cannot find any references to runtime in access or in windows list of programs.

I tried to publish the DB but all I got was a accde extension not the accdr.