Modules & VBA :: Pulling Data Using Query - Combine Two Functions Into Third Function

Jun 28, 2013

I created two functions to use when I'm pulling data using a query (QBE). I would like to combine the two into a 3rd Function that works like this:

BB_Only = DishNetActivityCode = 1 and VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 and VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 and VideoActivityCode = 0

Function DishNetActivityCode(service_code_string As String) As Integer
Dim CodeArray, i As Integer

[Code] .....

View Replies


ADVERTISEMENT

Modules & VBA :: Pulling Data From Other DB To Current DB

Jun 5, 2014

I'm trying to make a button on a form that pulls data from a table in a database on a network share to the current open database (with the form).

Database on network share info:DB location: est.com est estdb.accdb
Table in DB: Table1

The current open database has the exact same table:Table2.In the following VBA line I get Runtime Error 3126: Invalid using of bracketing in name '' (The quotes at the end are 2x ')

Code:
DoCmd.RunSQL "INSERT INTO Table2 SELECT [ est.com est estdb.accdb].Table1.* FROM [ est.com est estdb.accdb].Table1"

I tried everything with changing the [, " and even the path name of the external DB as variant of type string. Nothing seems to work.

View 3 Replies View Related

Modules & VBA :: Trimming White-space After Pulling Data From Excel Cell?

Apr 15, 2014

I've got a piece of VBA scripting which runs as an event linked to a button on my MS Access form.

I maintain a database of members of staff at my organisation. It's pretty outdated...

I'm basically wanting to pull in their updated data (extracted from on our payroll system) from a spreadsheet, into a form, when clicking a button on a particular person's record.

This is what I have so far.

Code:
start = Forms!frm_main2!txt_start_row.Value - 1
conv = DDEInitiate("EXCEL", "Staff List.xlsx")
cell_employee_number = "R" & start & "C1"
cell_surname = "R" & start & "C2"

[Code] ....

The function "CStr(DDERequest())" converts the cell number into the readable data, however I seem to have whitespace below the value.

What would I need to do to strip out this whitespace? Would I use strtrim? If so, I am unsure of the syntax... how would I incorporate strtrim into the above?

View 7 Replies View Related

Queries :: Query Not Pulling Through All Data?

Oct 6, 2014

I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number. I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).

The SQL looks like thiss:

SELECT [SM_Antennas_DL-1_Q].[Site ID], [SM_Antennas_DL-1_Q].[Antenna Type], [SM_Antennas_DL-1_Q].[Electrical DT], [SM_Antennas_DL-2_Q].[Electrical DT], [SM_Antennas_DL-3_Q].[Electrical DT]
FROM ([SM_Antennas_DL-1_Q] INNER JOIN [SM_Antennas_DL-2_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-2_Q].[Site ID]) INNER JOIN [SM_Antennas_DL-3_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-3_Q].[Site ID];

So the data DL-1, DL-2, DL-3 is from the same database.

View 8 Replies View Related

DatePart Function, String Functions

Nov 1, 2005

Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???

View 7 Replies View Related

Forms :: Pulling Data From A Query Based On Criteria

Apr 13, 2013

I am having a problem pulling some data from a query to populate text boxes in a form

Text27 = DLookup("'SumOfSumOfDocCount'", "SumTotalPerf", "DateReceived=" & Forms.Tracker.Text23.Value & "AND 'BookedInID'=" & Forms.Tracker.BookedInID.Value)

I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.

View 8 Replies View Related

Queries :: Conditional Query Pulling Data From Two Tables

Mar 6, 2014

So I have two tables:

users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...

user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...

In user_change I record any changes made to the users table.

What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.

I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.

This is the data I want to pull from user_change...

The MOST RECENT action_date WHERE change_type is LIKE "*issued*".

However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.

At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...

View 7 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Queries :: Combine 4 Tables Data That Falls Within Dates - SQL Query Returns No Records

Oct 2, 2013

I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:

Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]

[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.

I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records

To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.

View 1 Replies View Related

Pulling Data Out

Nov 7, 2005

I have a member roster. I have members who have attended. How do I subtracta list of those who have attended from the roster to find those who havent attended. The only way I know those who have attended is by their sign in date.

View 1 Replies View Related

Pulling No Data

Dec 28, 2006

How could I pull in someone when they do not have data?

There is a name table and data table. My goal is that when I run a parameter query if someone does not have data for that time period to have that name pulled in so that eventually a report could be generated stating there is no data for this person for the time period.

View 1 Replies View Related

Modules & VBA :: Field Names In Functions

Oct 23, 2014

Adding fields into a function has been a problem for me:

Code:
IIf((Count([qryADDR].[Fuel Type]>2), 'MPS', [qryADDR].[Fuel Type])

The logic I'm trying to create is the following: If the number of Fuel Types is greater than two, then the field will have the value 'MPS.' Otherwise, the field will just have the regular fuel type value. It's referencing this fuel type field in the function that is the problem. I have not been able to get this working.

View 5 Replies View Related

Pulling Data From A Table

Dec 11, 2005

I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions

jon

View 1 Replies View Related

Pulling Data From SQL To Access DB

Jan 23, 2006

Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?

View 3 Replies View Related

Data Pulling Problems

Nov 14, 2005

Hi,
Here is my problem. I am trying to use a form to enter data that is contained on one table, have it pull data from another table that is related to the original data, then add data and dump the whole thing off to another table. I would like to do this one item at a time. The only way I can get this to work is to pull all the data at once into the form. Is there any way I can pull just the related data I need by just entering the item number?

Thanks,
D

View 1 Replies View Related

Need Help Pulling Data From A Form

Nov 22, 2006

I have 2 Tables and 1 form.

Table 1 has:

Product ID #, Product Name , Catalog Number

Table 2 has:

Date, Product Name, Catalog Number


with the Form, You select a Product Name (From table 1) and you can enter a date, which is entered into the Date column in Table 2. The form will also transfer the Product Name to Table 2.

Now, the problem is I can't seem to get the Catalog Number from the Form to enter into Table 2 automatically. It enters the Product Name just fine. I can, in Table 2, click the field and select the Catalog Number from a list, but that's not what I'm trying to accomplish. When a Date is entered into the Form, I'd like it to update Table 2 with the Date, Product Name AND the Catalog Number.. automatically.

is this possible? and if so, how?

I sorry I'm really new to Access, so my terminology is all out of wack. I hope the question is somewhat clear.

View 2 Replies View Related

Search From Not Pulling Up All Data

Oct 1, 2013

I have a query and search form with this critera in each field for: Employee, Job Number etc.

Example: Like "*" & [forms]![F_SearchFormWide]![Job Number] & "*"

If I put in may the first 3 characters of the job it pulls it up but when I put the full number nothing comes up.

Also the DB has 40,000 records. The total at bottom of query is only 16,000.

I have tried verifying my join connections making sure all jobs are pulling from job table, I tried all 3 ways. Why its not pulling up all my records.

View 5 Replies View Related

Pulling Data From One Table To Another

Mar 16, 2012

I have created a database to track the paper files that I am accountable for. Every day I get requests from workers for various files. I have split the database so that multiple users can access the db and edit or change the status of files. In the be I have the main db with fields required for the files and I have another database (table 2) with similar fields that i use to generate an email form that workers fill out to request files. Access then automatically pulls that data from the email form and inserts in to table 2. I pull those requested files then enter the data in to table 1 so that I know the who/what/where of a files location. I want to be able to automatically insert info from table 2 into table one after I have pulled the file and checked it out.

View 2 Replies View Related

Modules & VBA :: Accessing Form Functions From Module

May 22, 2014

I want to put some form functions into a module.here is what I currently have in the module:

Public Sub Fun_Test()
Forms!Form_Output!Sequence.ColumnWidth = 250
End Sub

my form name is "Form_Output" but it still cant find it..

View 1 Replies View Related

Modules & VBA :: Unable To Call Excel Functions

Jul 15, 2013

I spent all day trying to figure out why sometimes I can call an excel function from access and the value will appear on my form, but for some functions (F.inv, binom.inv), it errors out as "object not supported". I browsed all the available libraries in tools/reference but found nothing that seemed to solve this issue.

View 7 Replies View Related

Forms :: Pulling Field Data?

Jul 11, 2013

I have a form and a subform. I want to pull the data (current record) from a field (Unit) from the main form and use it in a report query, which is connected to my subform.

I thought I could create a variable (strVarUnit) then call the variable in query but I'm doing something wrong.

On the main form in the open event I created the variable ...
Dim StrVarUnit as String

On the form after updating the Unit field I placed an event ...
StrUnit = Me.Unit

I then placed Call StrVarUnit() in the query.

This doesn't work (variable undefined).

View 3 Replies View Related

Forms :: Pulling Data From One Form To Another

Dec 26, 2014

I have a table/form [tblStdAdv] [frmStdAdv] that contains all advisers a student had during their program. that form pulls from refAdv which has:

Type
1- initial
2- current
3- previous

and then an Adviser field, per record

I need to pull the adviser onto the student's main page [tblStdInfo][frmStdInfo] either "current" (if they have had changes) or "initial" (if they went through without an adviser change) .

I would like to pull the info into the main page so that I only have to change, or add to, the one form able and the main form stays up to date.

i tried using a query and was able to pull either "2" or "1" or "both" but could not make it look for "2" Current first and if not found then use "1" Initial.

View 2 Replies View Related

Reports :: Pulling In Data From Another Table

Jun 2, 2015

I have two tables; one has just variances entered, the other is the total number of events for the month (normal events + variances). The variances become the numerator, and the total events is the denominator.The report does counts of variances based on location, type of variance, etc.This is working just fine, but I also need to show on the report the number of variance/total events (%).

I am struggling with pulling the data for total events into the variance report.The variances all have dates, the totals have start and end dates (monthly). For the variances, my query asks for date range, but I want this same date range to be used for pulling in totals from the other table.I am trying to use DSUM("monthlytotal", "tblTOTALS",Between "StartDate=input start date from report query" And "input end date from report query").Should I setup a form that after the start/end dates are enter, the report is launched using these dates?

View 5 Replies View Related

Modules & VBA :: How To Write A Function That Does Query

Jul 22, 2013

I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.

Code:

Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database

[Code]......

View 5 Replies View Related

Modules & VBA :: How To Run A Function From SQL Query In Access

Nov 26, 2013

I started using Access recently and I have an inquiry about VBA codes and sql queries. I have function which has the following signature: RAG(Stability as Integer, DS_SYNC_RATE as Integer, Profile as String) as String.

I would like to call this function in Insert sql query in order to calculate and add a new column to my table. I tried this basic query:

Insert INTO my_table VALUES (RAG(my_table.stability,my_table.ds_sync_rate, my_table.profile))

but it doesn't seem to work...

View 8 Replies View Related

Modules & VBA :: How To Use Split Function Query

Feb 18, 2014

I have a query

Code:
SELECT tblZlecenia.ID_Zlecenia, tblZlecenia.TerminOdbioru, tblOffset.ID_RodzajZlecenia, tblZlecenia.NazwaZlecenia, tblZlecenia.Id_K, tblZlecenia.Id_Rodzaj_Pracy
FROM tblZlecenia LEFT JOIN tblOffset ON tblZlecenia.ID_Zlecenia = tblOffset.ID_Zlecenia
where

In where I would like to put code like this:

Code:
Split(tblOffset.DziedziczonePo, ";")(1) & ";" & Split(tblOffset.DziedziczonePo, ";"(2) & ";" = "tak;tak"

Is it possible to create such a query?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved