Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sponsored Links:

Related Messages:
Declare Variable
we upgraded to 2003 access. Now in a code getting the msg that I need to
declare a variable for:xlexcel9795
here is how it is in the code. what do I put?

View Replies !   View Related
Server Variables In Access 2000
Is there a way to get the %USERNAME% variable into Access?

I am the current "designer" of an Access database, and there are certain functions that at the current time, only two of us should have access to... however, I do not want to password protect or create a security structure for the database if I can avoid it.

Is there a way to pull server variables from the local PC for use in path names (ie., ../Documents and Settings/%USERNAME%/Desktop/Filename.txt)?

View Replies !   View Related
How To Use IIf With Three Variables
Hi, here is a query which returns rolling sum for past5days.The resulting table has threee columns,namely,

MyDateFiled ,MyValue,Past5DaySum

SELECT tblData.MyDateField, tblData.MyValue,
(SELECT SUM(a.MyValue)
FROM tblData As a
WHERE DateDiff("d",a.MyDateField,tblData.MyDateField)
Between 0 and 4)
AS Past5DaySum
FROM tblData;

Someone help me in modifying the above query ,so that ,a Type(1,2,3) can be assigned to each Past5DaySum
based on the following criteria.Is,it possible to use IIF function for three variables ?,so as tp get a new table(Mydate,MyValue,PastDaySum,Type)

1, A<5 , A<20
2, 5<=A<=10, 20<=A<=40
3, A>10, A>40

LeanPeriod=Jun to Nov
GoodPeriod=Dec to May

View Replies !   View Related
Is It Possible To Use Variables In Sql?
Hi there, is it at all possible to use variables in sql? Please consider the following snippet:

... (WHERE [DateDue] < Date())...

The idea is to return a recordset where the DateDue is prior to the current date. But sometimes I need the opposite, ie a recordset where DateDue is in the future: ... (WHERE [DateDue] > Date())...

Right now I have 2 long queries which are exactly the same, except for the operator < for the first case above and the operator > for the second.
I of course did not want to repeat the queries so I tried:

Dim op As String
op = Me.txtOp 'txtOp is invisible and gets its value (either "<" or ">" ) thru
'a button click
... (WHERE [DateDue] & op & Date())...

I also tried:

... (WHERE [DateDue] & forms!frmInvoices!txtOp & Date())...

But to my "surprise" both failed.

Can it work? what should I correct or do differently here? I might need this kind of trick for other routines too, so I'd like to avoid copying the same query, just for the operator change.
I hope one of you seasoned sqlers can tell me more about it, I appreciate.


View Replies !   View Related
Sum IIf And Three Variables
I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.

Here is the formula I have constructed that does not work.

ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))

Any suggestions?

View Replies !   View Related
Using Variables
I am trying to incorporate the following code into my code:
Code:If Not QueryExists("Q_ABC") Then Set qdf = db.CreateQueryDef("Q_ABC") Else Set qdf = db.QueryDefs("Q_ABC") End If
Difference is that I want to use a variable to provide the query name...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists(queryname) Then Set qdf = db.CreateQueryDef(queryname) Else Set qdf = db.QueryDefs(queryname) End If
but this does not work... but neither does this...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists("queryname") Then Set qdf = db.CreateQueryDef("queryname") Else Set qdf = db.QueryDefs("queryname") End If
nor does this...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists queryname Then Set qdf = db.CreateQueryDef queryname Else Set qdf = db.QueryDefs queryname End If
Can someone please show me how this is to be done?? I feel that it must be something simple...
Thanks a lot (in advance)!

View Replies !   View Related
SQL Variables
The following code recognizes the value of 'TheYear' after running the line of code (ie. "#11/13/2007#"). The problem is it doesn't seem to recognize it in the SQL, but rather asks for a value when I reach the SQL code line. How can I get it to substitute automatically? THANKS!
DoCmd.SetWarnings False
TheYear = "#11/13/" & Year(Now()) & "#"
DoCmd.RunSQL "Update [Employees Trained List] SET [Employees Trained List].[This Years Training Date] = Null WHERE (([Employees Trained List].[This Years Training Date])< TheYear );"
DoCmd.SetWarnings True

View Replies !   View Related
Holding Variables
I have started holding variables in text boxes on forms - (variables needed at module level for that form).

Is this a correct thing to do.

The benefits I have are not loosing the value should some error occur - and being able to reference it from another form.



View Replies !   View Related
Query Using Variables
I am currently making a project as part of my Computing course. The project is a running diary, where users can log in and record their running times(Amongst other things). As part of the system i am going to include a calorie calculator, to do so i have to work out the BMI (Body Mass Index). To do this you have to divide the weight by the square of the height. The weight and height are stored in the tables.
I am planning on creating a form for this conversion which will refer to a query. When the users log-in their ID is stored in a Public Variable called "UserID".
What i would like to do is query the database for their height and weight using this variable, but i don't know how to go about doing this.
Does anyone have any suggestions?
Many Thanks

View Replies !   View Related
Linkcriteria Using 2 Variables?

Currently i use one variable as indicated below from a continuous sub form field to open another form

Dim linkcriteria As String
Dim frmRoom As String
frmRoom = "frmRoom "
linkcriteria = "[HeadCloseID] = " & Me.HeadCloseID
DoCmd.OpenForm frmRoom , , , linkcriteria

however, after restructuring some tables, i need to open the form with respect to 2 variables instead of the previous one.

hence my question is can, how, and what code would i use to do this

many thanks in advance

View Replies !   View Related
Global Variables
I have an access form called forma. It has a command button. When I click a command button, FormB opens up and FormA closes. The FormB also has a command button. When I click the command button of Form B, it closes and opens FormC. Like this there are total 30 forms. What I need is. I want to count the no. of clicks of the command button in total. Actually each button is a correct option of A multiple choice question. I tried to declare a public Variable called total in the declareation of the first form and tried to use it directly in the succeding forms. It does not capture the values. How can I increase the value of the varialbe declared in the first form even after the form closes and capture till end. Actually I also should be able to send the value in a table. Please Help Me.!

View Replies !   View Related
Dynamic Variables
On a form which includes a subform -- I have one field (text box A) which is dynamicly populated by the values of 4 combo boxes. Once it is filled and you set focus to the subform, the information from text box A goes to the subform (which is cool and what I need it to do...) but how do I get information from text box A on the primary focused form (form A) to populate the appropriate table (table A)?

Lets say for simplicity, Foorm A is the primary form and Form B is the subform...likewise, Table A is the information warehouse for Form A and table B is the information wharehouse for form B...

When text box A gets filled with it's information, how do I get the information to go into table A when it's control source is being used to populate the information from the combo boxes?

View Replies !   View Related
Environment Variables In 2007
Just updated to Access 2007 from 2003 and my app does not pull in any environment variables. =environ("variablename")

Anyone else have this problem?

View Replies !   View Related
SQL Pass Through Form Variables
I know this is probably obvious but how do I pass through form variables to sql server I currently have:

exec QStudent @_param_cmbYear="0708", @_param_SelID="S", @_param_SelForename="d", @_param_SelSurname="S"

this executes correctly SelID being a student ID or partial ID also allowing for surname forename partial search. I thought it would just be:

exec QStudent @_param_cmbYear=[Forms]![Attendance and Lateness Main]![cmbYear], @_param_SelID="S", @_param_SelForename="d", @_param_SelSurname="S"

to switch acad year to a form variable and repeat for the others but it errors on trying to save the pass through. Thanks for the help.

View Replies !   View Related
Can Variables Be Used In A Form Control??
hi all, Newbie to access and am making an asset tracking database for work. I have written a sub that opens a hidden form and queries it. I want to get a value from that hidden form and stick it into a combo box on my origianl form. I have got most things to work. The problem i am having is using a variale in a control form i.e. [forms]![ & strVar & ]![Field] here is the code i have so far
Private Sub Form_Search(strFname As String, strField As String, strFField As String, strWhere_Next As String)
Dim strWhere As String
strWhere = strField & " = " & "" & strFField & ""
strWhereA = "[Forms]![" & strWhere_Next & "]![ALLOCATEDTO]"
DoCmd.OpenForm strFname, acNormal, , strWhere, , acHidden
Me.ALLOCATEDTO.Value = strWhereA
DoCmd.Close acForm, strFname

the strWhereA shows the correct field but is shown as [Forms]![Formname]![ALLOCATEDTO] in my combobox, not the actual value of that form. Please help. I know this is probably a simple syntax error but am now at my wits end!!!

Thanks in advance!

View Replies !   View Related
Global Variables As Criteria
Anyone know how to reference a global variable as criteria for a query?

I am currently using invisible text boxes on an intermediary form as criteria for my query and it works fine, but this solution seems inellegant and I'm looking for something a little smoother. I just need the correct syntax to reference a variable in the criteria section of a query.


View Replies !   View Related
Using A Form For Criteria Variables
I have a table Names containing Recnum and Last Name. I have a table Grades including Recnum, Names and Grades. I have a Report which Through a query Reports the grades and averages for each name. I would like to be able to query for an individual name using an entry form. How do I get the name from the form into the Criteria in the query grid.

View Replies !   View Related
Multiple Variables In Query
I've built a pretty extensive database for work thanks largely to the folks on this board. But I'm stuck. I have a report to track inspections and it works fine, but I'd like to filter it down by AFSC (a coded expression the AF uses to denote career fields, i.e. 2A5x1x is Aircraft Maintenance.) The report's queries come from a couple of sources to retrieve the requirements for the report. For instance that AFSC has to start w/ "2A" and the labor code needs to start w/ "1" and not be "120". No problem. The problem I'm having is that the first 'x' (from the AFSCs below) is based on skill level (can be a 1, 3, 5, 7, or 9) and the last x is sometimes there and somtimes isn't.

For this particular report I've broken down the AFSCs down to three different formats. They are as follows:

2A5x1x (i.e. 2A531B or 2A571)
2A5x3x (i.e. 2A533A, 2A55B, 2A57C)
2A6(or 7)x1(or 2,3,4,5,or 6)

The report is designed to show who is due an inspection and it works fine. But I need to try to make it a little more user friendly so that all the various workcenters can trim it down to view just their career fields. Right now it reads from a query.

Here's the SQL for it.

SELECT qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC AS FilterAFSC, [Employee List Table].[Labor Code] AS FilterLaborCode, qryPEDueUnion.[Inspection Type], Last(qryPEDueUnion.Date) AS LastOfDate
FROM qryPEDueUnion LEFT JOIN [Employee List Table] ON qryPEDueUnion.[Main Assessee] = [Employee List Table].EMP
GROUP BY qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC, [Employee List Table].[Labor Code], qryPEDueUnion.[Inspection Type], Left([AFSC],2), Left([Labor Code],1)
HAVING ((([Employee List Table].[Labor Code])<>120) AND ((qryPEDueUnion.[Inspection Type])="PE") AND ((Last(qryPEDueUnion.Date)) Between DateAdd("m",-19,Now()) And DateAdd("m",-15,Now())) AND ((Left([AFSC],2))="2A") AND ((Left([Labor Code],1))=1))
ORDER BY Last(qryPEDueUnion.Date);

Let me know if you need more info. I'm new to posting on this board and haven't quite found out how to attach files....Plus this db is pretty big and I'd have to fool around w/ a copy of it first to make it small enough to attach.

View Replies !   View Related
Passing Variables To Queries
Hi, I am writing a script which will retrieve all of the tracks relating to whichever cd the user has chosen. The script is being written in asp and the line which sends the variable to access looks a bit like this:

sqlQuery3 = "up_getAlbumTrackInfo " & productID
Set rs3 = dbConn.Execute(sqlQuery3)

If possible could you tell me how to retrieve this value from access as I have become lost.

At present the SQL code in the query looks like this

SELECT tblTracks.trackName, tblTracks.TrackNumber FROM tblTracks
WHERE tblTracks.productID = (** variable would go here **)

Many Thanks


View Replies !   View Related
Calculate Days When There Are 4 Variables
I have a query that has four date variables:

Admit Date
Discharge Date
Report Begin Date
Report End Date

I can calculate the number of days with the DateDiff function when the "Admit Date" and "Dischage Date" are within the range of the report with no problem.

The problem arises when the "Admit Date" is prior to the "Report Begin Date". In this case, the difference needs to be "Report Begin Date" and "Discharge Date".

To make this more complicated, in some cases there is no "Discharge Date", so the calculation needs to look at the first two senerio's and then calculate the days based on the "Report End Date".

Expr2: IIf([Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))

Expr2: IIf([Admission Date]<[Beginning of Report] And [D/C Date / Time]>[End of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))

Expr3: IIf(IsNull([D/C Date / Time]) And [Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[End of Report]),DateDiff("d",[Admission Date],[End of Report]))

expr4: IIf([Admission Date]>[Beginning of Report],DateDiff("d",[Admission Date],[D/C Date / Time],0))

Above are failed attempts to get the formula right.

Can anyone assist me in getting this to work correctly.


View Replies !   View Related
SELECT SQL Query With Variables
Dear All,

I am trying to perform a SELECT query in access vba to show me customer account numbers in a msgbox.

I get a Run-time error '13': Type mismatch.

I would appreciate any help. Please see below for examples of my code.

'CustomerID is a Global Variable that gets it's value from a DLookup that gets triggered after a combobox has been selected.

The combobox does the following
Private Sub AfterUpdate_cboCustomer
Dim iCustID as Integer
iCustID = DLookup("ID", "tblCustomers", "Customer='" & Me.cboCustomer & "'")
iCustID = CustomerID
'Returns the Customer's ID Perfect

Dim strSQL As String
strSQL = "SELECT AccNo FROM tblAccNo WHERE CustID" = CustomerID
DoCmd.RunSQL(strSQL)' Errors Here

End Sub

View Replies !   View Related
Payment Date With To Many Variables?

We have a special reduced rate that a client must prepay 12weeks before or sooner if a date is put in the [OverRideDate] field

I have been trying to construct a Query / If statement or should I use case statement to work out the actual payment date. Taking in to consideration all our funny little variables like

I will try to put the variable into words

[DateOfArrival]-84Days unless [OverRideDate] & Not Less than today
then check its not a [ClosedDates] from the tbl_ClosedDates
Then if itís a Sunday Ė1 day as the accounts lady doesnít work on a Sunday

Their simple????

Attached file with sample variable data


View Replies !   View Related
Refer To Controls Using Variables
I have a form with 25 related sets of text-box controls:

Product1, ProdDescr1, ProdQty1
Product2, ProdDescr2, ProdQty2
... Product25, ProdDescr25, ProdQty25

I need to loop through all 25 to check that the ProdQty for each is correct, and if any of them are incorrect, I'd like to set the BackColor to Red and the ForeColor to White for that particular set of controls. Rather than coding all 25 checks, I'd like to use variables to loop through the 25 sets

I understand how to create an incrementing Integer variable, but what is the correct syntax for combining that Integer variable with the String variables in order to perform the Back/Fore color commands?

View Replies !   View Related
Global Variables In *.adp File
i have two files (test.adp and master.adp). the test.adp file is for testing (duh) and the master.adp is the file my coworkers use to enter data. i created several forms for data entry/retrieval in my test.adp file. in one form (building) the user selects the room and building they need. i store the room number in a global variable (gRoom). when they double click on the room they need, a second form (audit) opens where they enter any necessary information related to this room. the global variable gRoom is used to insure a room actually exists. what i mean is, if nothing has ever been entered for that room, when form audit is opened the information for the room number is checked - if empty then the global variable gRoom is entered for that locale.

this works 100% fine in test.adp. i've debugged and followed the variables through the process and it works. so, what i've been doing for the past year is opening master.adp and going to File > Get External Data > Import and select the forms i tested in test.adp and import those. this has always worked until these new forms. i import them over and when i debug, gRoom has a value in the building form but it is not carried over to the audit form. according to the audit form, gRoom is Null.

what is happening to my global variables? :confused:

please help...

View Replies !   View Related
Programming With Control Variables
The following code is only for testing the usage of control variable. I want to use set statement to assign the textbox object reference to the varialbe myControl.

When I clicked the button, the system always tells me 'the object reference missing'. It seems the set statement doesn't work, but I don;t know the reason. could anyone of you please spot it out for me?

Many thanks

Option Compare Database
Public myControl As control
Public frm As Form
Set frm = Forms!Form1

Sub Command4_Click()

Set myControl = frm!Text0

End Sub

Sub Command5_Click()
Set myControl = frm!Text2
End Sub

View Replies !   View Related
Query Syntax & Use Of Variables

I've become aware that if I create a variable in the select statement like this in Access:

SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum

That I cannot reliably use said variable later in the same statement:

SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum
FROM Table1

It will ask me to "enter a parameter value" for TotSum. When I do, in this example, it still sorts in correctly, but in a larger more detailed query it gets a little confused. I instead have to re-use the equation like this:

SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum
FROM Table1
ORDER BY Sum([A]+[B]+[C]) DESC;

Is there a way around this? It seems inefficient to recompute the sum 2 times where I think I only need to do it once.

Any input or explanations?


View Replies !   View Related
Passing Public Variables
I'm into one of the subforms that will be using the public variable from the main form and am not having success passing the data from the main form over. on the subform, i created a textbox (txt_currentyear) w/ this in the control source field: =[WrkYear] & " Golf Outing"

WrkYear was the defined in a module as:
Option Compare Database
Option Explicit

Public WrkYear As String

I have created a listbox w/ a few years in it (current_year_listbox). this has =[WrkYear]=Me.Current_Year_listbox.Value in the after update field. i loaded the main form, selected the year, went into the subform to see if it passed the data along w/ no luck.

do i need to call or reference that module in every form or report before i can use the data from it? right now, i just get an empty field on the subform.

View Replies !   View Related
Variables And Record Counts
I have a telephone database with call detail records for every call. In my criteria selection, I am selecting only inbound (CallType 2), and (outbound CallType 3) call records. I need to count the total number of records in each CallType. I have very little experience using a gui type database management software like Access, (I use to use DB2 on an AS400). I am trying to create a variable like you would in VB and use it in Access put can't figure out how to do that.

Little background on the database project: In our customer service center, I need a report that displays the total number calls for each customer service rep, inbound, and outbound calls, for a specific date range. CallType 1 = intercompany calls (which I want to exclude), CallType 2=inbound CallType 3= outbound.

View Replies !   View Related
Working With Time Variables
I need to have my form give he user a Start time and then an End Time.

How do I store the variables (stirng, integer?)

How do I subtract the End Time from the Start Time to get a total time??

I then need to Multiple the resulting time frame by a Rate to calculate total value...

Any thoughts?


View Replies !   View Related

Copyright © 2005-08, All rights reserved