I am working on a DB and would like to include the following information on a report. The information is as follows;
tblPersonnel.Name Tony Jennifer James Tom
tblPersonnel.LOA tblPersonnel.Vacation
Tony is on a Leave of Absence (LOA) Jennifer is on Vacation which leaves James and Tom at work. Lucky them! The fields "LOA" and "Vacation" are two separate check boxes.
I would like to limit the query to display employees that are either on a "LOA" or on "Vacation" Since they are in two separate fields how do I write the expression? I know how to limit it to only employees on LOA. Or employees who are on vacation, but how do I qry those who are on vacation or LOA?
I have a Union query that brings together several tables into one. This works perfectly well however there is an occassion when a field in one table has its data type changed to text from date. This is to allow N/A to be entered rather than a date. When this occurs the union query changes all the dates from the other tables to text also and therefore other queries I am running based on the Union query now fail.
Is there any way to have the Union Query ignore the data from the table where and when this this occurs as it is not necessary to show in the query and can happen with any of the tables at a users discretion
I have a query that selects invdate, status and type from a table, I want to be able to search for invdate=today status between 50 and 70 and to count types 30, 31+32, 33+34 and 35.
I have used Allen Browne's code (see http://allenbrowne.com/ser-16.html, bottom of page) as a model for my user-defined type and public function in an events management database.
Using the function in forms/VBA works fine.
But my DB crashes whenever I try to use it in a query.
My modules code: = = = Option Compare Database
Public Type EvCheks EvType As String EvAttCat As String EvUnitAss As String EvCheksAll As String End Type = = Public Function getEvCheks(EV, EvUnit) as EvCheks
'Event Type: Event or DL If (DLookup("evtype", "tblevents", "[evid] = " & EV)) = "Event" Then getEvCheks.EvType = "Y" Else: getEvCheks.EvType = "N" End If
'Event Attendance Category: INDB= in database or LIST If (DLookup("evattcat", "tblevents", "[evid] = " & EV)) = "INDB" Then getEvCheks.EvAttCat = "Y" Else: getEvCheks.EvAttCat = "N" End If
'Event Assessing Organisation Dim AOROName As String Dim AOName As String AOROName = DLookup("evunitassessable", "tblevunits", "[evunitid] = " & EvUnit) AOName = Mid([AOROName], 1, InStr([AOROName], "/") - 1)
Select Case AOName Case "NA" getEvCheks.EvUnitAss = "N" Case "ABCD" getEvCheks.EvUnitAss = "Y" Case Else getEvCheks.EvUnitAss = "X" End Select
getEvCheks.EvCheksAll = getEvCheks.EvType & getEvCheks.EvAttCat & getEvCheks.EvUnitAss End Function ==== On a form, I have a button with on-click event code that defines the EV and EVU variables and displays a message box MsgBox getEvCheks(EV, EVU).EvCheksAll
This works fine. ===== In a query I have this SQL code: SELECT tblEvUnits.EvId, tblEvUnits.EvUnitID, getevcheks([evid],[evunitid]) AS EventDetails FROM tblEvUnits;
This causes the db to crash.
I cannot get the "back-end" definition of the function into the query, as Access rejects it because of the dot, i.e. ".EvCheksAll".
I suspect there is some formatting quirk for using a public function that includes a user-defined type, in a query.
I am working in MS Access 2000. I have a query that is returning a table with various types of data. The problem is I can't seem to find anywhere in the Access GUI that will show me the data types of columns it has returned so I can't manually create a table to hold the values returned by query. Question is two fold:
1. Is there a way in Access SQL to import data returned from a query into a table that is not yet defined. Some like this: a. Create a table that has a primary key but doesn't yet know the number of columns and/or data types of those columns. b. Import the data from a query into this table and have it create the columns and copy the data types and populate the table while also numbering each row by primary key.
or 2. Is there a way to find out what the data types are for a table returned by a query in MS Access. I have checked the properites of the query and have been up and down the gui looking but I can't find a way while looking at the data returned by a query to explicitely see the data type (i.e., number, Date/Time, etc..) used by each column.
This the first time I post in this forum and I tried looking for an answer to my question before posting, so I apologize if my question has been answered previously.
Is it possible to determine, in Access 2000, the data type in a Make-Table query of a new column with null value so that it does not default to binary.
Here is my sample sql:
SELECT tblAddressBook.Name, Null AS Email INTO tblTestTable FROM tblAddressBook;
The output is tblTestTable with two columns: Name and Email. The data type of column Email defaults to Binary. How do I make it default to Text.
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
SELECT IIf((Count([PO]))>1,1,Count([PO])) AS PO_Num, IIf((Count([PO]))>1,'Multiple',[Category2]) AS Cat FROM [Final] GROUP BY [Final].Category2, [Final].[PO];
k so stage 1 seems to work, all the PO's that are multiples for the one category have been grouped and their value set to 1... but there are PO's with the same number pointing to another category.
I need to "simply" merge those PO's to be just one line_item.
Information 8|REPORT BY THE COMMITTEE APPOINTED TO STUDY CLASSIFICATION, TRANSPORTATION, DISTRIBUTION STORAGE AND SALES OF PESTICIDES IN *******|***** *. *****|******** ** *** **********|****|||PA2|
Okay, so my office had this emergency data dump, we lost the library, the records were saved but to do so they had to be backed up as .t files :-s which means less formating then a txt file :eek: :eek: :eek: , anyway, my office is hopeful I can fix their problems, sorry bout the ***'s, I was told that I can not put that information up on the net for security/privacy purposes. Anyway, what I have to figure out to do is to take this 1 field and split it into 8 fields, these correspond with the | character. If you see 2 || or more together it means that field was empty. Anyway, I just need a way to break these into 8 seperate fields, any feed back would be great.
Apologies ... but I am quite new to Access. My database contains about 1000 peoples' addresses.
The problem I have is that I need to merge, say, 20 of these addresses into a word document. In the word document I can select records using the "Surname" field but it will not give me the option to select more than 6. I am sure there must be a way that I can select more than 6: I have tried separating the surnames with a "," or "or" but nothing seems to work!
I was wondering if there was a query to run that would seperate a field in a column into to.
The Current Field is Prec and the entries all are numeric numbers followed by a dash and another numeric number i.e. 9700064-0 7600049-0 Another 7 digits-one digit I would like the first 7 in a column called prec1 and the one after the dash in a column called grp
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode: ? me.fsubsrNavSHLBHL("NavSH_QQ") NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line: fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window: ? me.fsubsrNavSHLBHL.Form.RecordSource Select * from vsrNavigatorSHLBHL where Well_ID =91229
Is it possible to have 2 sepaerate sub forms, with different source objects on a single form. I thought it was, but every time I save the main overall form, the source object of the second subform changes to the source object of the first.
Hey all, For securing a database, is it possible to set a certain password for users to view just one table? like i've lots of tables in my d.b that i plan to use a workgroup file for different users to view it, but there's one that i only want certain people to access...can this be done at all??
I have imported data from Outlook. The field I am interested in is the 'Subject' Field. This contains 3 effective segments, 1 employee name 2 description 3 job and sub job number
I wish to move the employee name into another field and also the job and sub job number. I have tried the following Right() Function but it is not quite working as I want
I'm making a table with a Yes/No choice many fields which represent subscriptions.
The problem is that the subscriptions available will need to be changed fairly often - will this mean the table and the form associated with it need to be redesigned?
If so, I don't know how to make fields that change depending on a different table.
If this can't be done, I'd be interested to find out how else I could go about this.
Is it possible to create a database and link this to a form and another database? What i want to do is link a network database with a local database on a client machine?
I want to import a csv file and store the good data (which matches input masks, validation etc) into one table, and all the bad data which is rejected into another table.
I've read a bit about an import error table, and although I do get errors when importing the csv file, no such table is generated.
I'm working with a linked table that has a seperate field for the date, and a seperate field for the time (which show in the linked table as '11/30/1899 9:46:00 AM'). I need to calculate the time difference between 2 entries.
How can I merge the 2 fields into a combined date/time field for my calculations?
Hi. Having a bit of a problem getting a subform to requery/refresh after running a query from a seperate form. The seperate form is acting as a 'search form' and running query qSearch.
I've tried various things (requery the form, subform etc) but here is the code I'm currently using:
Private Sub RunqSearch_Click() DoCmd.OpenForm "Papers" Forms.Papers.RecordSource = "qSearch" Forms.Papers!Authors.Form.RecordSource = "qSearch" Forms.Papers!Authors.Form!AuthorName.ControlSource = "qSearch.AuthorName" Forms.Papers!Authors.Form.Requery Forms.Papers.Requery DoCmd.Close acForm, Me.Name End Sub
The main form is requerying fine, but the subform isn't. Any help would be appreciated. Thanks
I am learning access on my own using MS Press step by step manual and it tells me to hold down the shift key while selecting the text boxes that are next to the labels, but when I do the labels get selected also . how do I disconnect the labels from the text boxes in order to insert space between the labels and the text boxes as it intructs me to in the book. I am able to select the labels separately, but it will not allow the text to be selected separately.
I want to be able to find the last value entered into a table from a form that is not based on that table. How do I go about this?
What I am trying to do is produce a 'purchase order number system' that adds 1 onto the previous number, but this purchase order can be generated on many forms and for many reasons across the database. I do not want to use autonumber.
Still working on a products catalog. I am creating this by printing a couple of separate reports, each with its own category.
At the end of a report A I store a value (number of pages) into a variable "NumPage", defined in Visual Basic. I have made this variable public and also the sub in which it is altered (report_close()) is public.
Now I should have stored the number of pages of report A in NumPage, enabling me to start report B with the right page. However in the public sub (on_open()) of report B I am not able to display the value of NumPages (just using a messagebox to check if it works).
Does anyone know whether it is possible, and if so in which way, to pass a variable created in a module of report A to report B?
I am trying to create a page that can display 4 different records from a database, each one in a different div container, for a news page.
I have an access database containing news records - headline, news and date. Each record also has its own auto-numbered ID.
So far I have managed to get the asp page to display all of the records in a list by using:
recordset.movenext Loop
and I have managed to get just 1 displayed by using:
SQL = "SELECT * FROM tblNews WHERE ID=1" and changing the ID number.
But I can't seem to find a way to display each in a different div container on the same page. Can anyone help please? I think this is really obvious but I just can't see it!