If you've got a library database with three tables: books, clients, borrowings, would it be okay to have a field in 'books' that flags whether or not the book is currently on loan - or should that fact always be elicited from a search of the 'borrowings' table?
View RepliesI am trying to automate a membership status flag based on comparing today's date and a recorded expiry date. The expiry date control is on a sub-form. I have the following code in the OnLoad event of the master form:
Dim DateGap As Integer
While Me.CurrentRecord < Me.Recordset.RecordCount
If Not Me.NewRecord Then
DateGap = DateDiff("y", Date, Forms!PersonalMasterF!MemberSubF.Form.MemberExpire )
Else
DoCmd.GoToRecord , , acNext
[Code] .....
With debug pointing to the DateGap = statement I get the error 'Invalid use of Null'. As you can see, I've tried to trap any new records it might run into to avoid nulls in MemberExpire, and there are no null values in the MemberExpire field of the underlying table. I've also tried defining DateGap as Variant, which does not work at all.
Hi, just so you are aware, I am totally clueless when it comes to Microsoft Access. I tried finding tutorials and help online that I could use to do this but I'm having trouble understanding what I'm supposed to do. I have two huge tables linked to each other by an ID number. One table contains multiple records with the same ID number while the other does not. There are records in the first table that don't appear in the second one, and there MAY be entries in the second table that don't appear in the first table. The second table has a flag that I would like to add to records in the first table that appear in the second table. What is the easiestt way to do this? This may be an extremely stupid question or it may be very hard, I'm not sure which, though I'm leaning toward it being a stupid question. Thank you in advance for your help.
View 4 Replies View RelatedI have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
Dear All,
I have a MAIN table which stores the most recent info of a record with
following details:
Unique_Ref_Num|Status|Dept
1 |6 | 1
and a second table called history which records changes in the main table
HistoryID | Status | DateStamp
1 | 1 | #22/10/2007 09:00#
1 | 2 | #22/10/2007 09:01#
2 | 1 | #22/10/2007 09:05#
2 | 2 | #22/10/2007 09:06#
1 | 2 | #22/10/2007 11:00#
2 | 3 | #22/10/2007 15:00#
1 | 3 | #22/10/2007 16:00#
2 | 2 | #22/10/2007 16:10#
Where Status 1 = Open, 2 = Allocated and 3 = Closed.
----------------------------------------------------------------------------------------------
I want to get the count of number of queries which are not closed
(outstanding) at any point in time.
Example: (with a time parameter)
Input | Result
22/10/2007 17:00 | 1
22/10/2007 16:05 | 0
22/10/2007 14:00 | 2
I want to achieve this with just 1 query (not by using one query within the
other) b,coz I want to further use this query from Excel VBA (write through
Excel VBA and not store the query within Access)
Any help will be greatly appreciated
--
Many Thanks
Baapi
:confused:
I have some tables with data where in one column the user will choose a status between 30-40 different options. They are choosing this option to show a reason why a trade was pended and not approved immediately. These reasons seem to change frequently and sometimes we no longer want to use an old reason.
I do reporting back several years, so I cannot just 'remove' a reason or it will be removed from the table and I will have blank reasons. When selecting the specific reason, the table takes the dropdown from another table that simply lists every possible reason. I want to know how I can make it so that the reason that is no longer in use remains on that connected table, but when the person entering data clicks the dropdown button, the removed or inactive reason no longer shows up as an option.
Previously I had seen people add a column to the connected table of reasons and use a "yes/no" check box to show if the reason is now Inactive. When you checked YES on inactive, the reason would disappear from the dropdown list. My issue is that I do not know what language or formula to use (or where to use it) so that when I click Inactive = Yes, the reason disappears from the dropdown menu.
I have a query that gets it's data from tblContacts. In this table is a yes/no field for archiving and the query gets only those records with a No in the field. The default is no. Next I have a form based on this query, which I am using to hold a set of command buttons which act on a contact chosen from an unbound list. So far so good but now I would like to add a command that changes the contact's status in the archived field of the table to Yes, so that I can use the form to view/edit, or email, or archive.
View 1 Replies View RelatedSorry, I tried searching the forums with no luck.
What I need to do is.
I have a table with a field called intervention day, the user then types in that field a date, pretty simple huh.
Well anyway I need the system to some how alert the user when that date comes, either through an information box or whatever.
thanks for any help,
tom harper
I need a flag(indicator) for my database. This flag would be read by many different functions and depending on the value of the flag, would do one of two things.
Example: If the flag is "A", when I click a button in a form it does "C" function. If the flag is "B", then in case of the previous button it will do "D" function.
What I'm having a hard time with is where to store said flag. Mind you the flag is either one or the other ("A" or "B") for the entire database, not just a specific record(s). I thought about adding a field to my 'master' table and then use an update query to change all the values from "A" to "B" as needed, but that seemed a bit like storing redundant data.
How or where should I store this flag?
Hey there
there is a function that I wanted to do on my form but can't think of a way
what I want to do is to show up a flag after 14 days and 28 days of a date with missed appointment being ticked
say there are two fields in the section
AppointmentDate
Missed
Will I be able to do this in access?
Please advice
Cheers
Si
hi I was wondering if anyone knows how to flag dates in access. I have 2 dates entered and I need a flag to appear if the second date is a month longer than the first date entered. Hope this makes sense and hope you can help
View 5 Replies View RelatedHow can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies View RelatedI print a certificate for each person attending my class by sending the form (StudentForm) to a report . To save time I print and sign certificates for all preregistered students then during class I enter all students that enroll the day of class. What I would like to do is flag each preprinted record so when I select the print button after all students are entered into the database all certificates except the preprinted records will be printed. Any ideas on how to flag a printed record?
View 4 Replies View RelatedI have a list of locations in a sub form where a chart has been and the last record is it's current location. I'm generating a building report which should show a list of charts on that building, but it also shows it on old locations. So in the example, it shows for building 43 and building 83. I just need it to show on 83, how to accomplish this.
View 2 Replies View RelatedHi everyone,
I'm aware of the wizard in MS Access that creates a new table containing duplicated entries that have been entered under one column, however, my problem is slightly more complex...
A contractor of mine has recorded information from CCTV surveys of sewers (not very glamorous I know!) in a Microsoft Access database. If you can imagine - the camera can be pushed along a pipe from both ends. Sometimes, the survey is abandoned in one direction and then repeated along the same length of pipe but from the other direction. The database contains (amongst other columns) a "start manhole" reference and a "end manhole" reference.
eg:
16014
As you can see, the information contained by both entries will be the same (as they are the same length of pipe). Therefore, I was wondering how I could get MS Access to:
1. Recognise these 2 entries as duplicates
2. Create a table that hides the duplicated entry with a shorter "SectionLength" value.
Many thanks in advance.
I need to build a Query that will look at a previous table and a new table and flag any changes...what is the best way to go about something like this?
View 5 Replies View RelatedI'm using MS Access 2010, how to flag a criteria in an age field like, Pop-up if age is under 14??
View 3 Replies View RelatedI have a knowledge base database which lets the user search for articles containing answers to common problems and issues. Some of the users want to be able to bookmark certain useful articles. The DB is a front end/back end design so I am thinking if I have one table stored in the front end which can be used to store that particular user's favourites then that would be great. However, I a little stuck on how to implement this. Ideally, I'd like a simple checkbox option next to each article, which when ticked, would store that article ID in that user's local front end.
View 3 Replies View RelatedI'm trying to create an access database to make an inventory of my model trains.
I have a main entry form (frmTrain) where I enter all sorts of info regarding e.g. a locomotive. This info is then stored in a table (tblTrain).
In the main entry form, I've put a combo box (cmbCountries) linked to a query (qryCountries) which queries the country codes from a table (tblCountries) that has three fields:
ID (autonumber)
CountryCode (short text) (containing the country codes UK, FR, DE, ...)
FlagFile (short text) (containing the name of the flag picture, e.g. UK.png)
The flags are stored as *.png files in a folder Flags that is in the same folder as the database file. I have chosen this approach instead of putting the flag pictures in an OLE field in tblCountries because I'd like to avoid being stuck to *.bmp files (don't support transparency). I'd also like to avoid having to mention the complete file path in the field FlagFile
I created a form (frmCountries) to easily add countries to tblCountries as needed.
Now back to the main entry form. The selection made in cmbCountries is stored in the field 'Countries' in tblTrain. When a country is selected in cmbCountries, I'd like that the corresponding flag is displayed next to the combo box.
I found an example on the web where an image field was used to display the flag, let's say with the following code:
Private Sub cmbCountries_Change()
Me.ImageFieldName.Picture = Me.cmbCountries.Column(2)
End Sub
Private Sub Form_Load()
Me.ImageFieldName.Picture = Me.cmbCountries.Column(2)
End Sub
and where the combo box had as row source (not using qryCountries):
SELECT tblCountries.ID, tblCountries.CountryCode, [Application].[CurrentProject].[path] & "Flags" & [FlagFile] AS Expr1 FROM tblCountries ORDER BY tblCountries.[Code];
The problem with this example is that, if you select in frmTrain e.g. UK, the UK flag is then displayed across all records in frmTrain. So the image field is not the appropriate field to display the flag in frmTrain and I guess an unbound/bound (?) object frame should rather be used.
How to display correctly the flag picture for every individual record in frmTrain corresponding to the country chosen in cmbCountries.
How to implement the procedure of clicking on the flag and hide the field in VBA? I know that want to assign - ColumnWidth=0.
Сomplete procedure fails.
I have a stock-type record and a stock-item record.
I want to update a flag on the stock-type record called instock to be true if there are related stock-item records and false if there are no related records.
Does anyone know how to run a updating query that will do this?
I am fairly new to this so I'm sorry if this is a bit of a basic question.
Thank you for your help in advance.
John
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
Another question on how might be the best way to do things with dates ?
I have a simple database which is a fault database.
I get an email request to investigate something and I log it and give it a ticket number. If I email back , I put the comments that I sent in the email in the update field on the database.
When I create the ticket its in an open state with a date and time stamp of now()
When I close (drop down box with open and close as options) the ticket it automatically updates the time in the closed filed to Now()
What I want to do is when I updated my comments field , this sets a date value in a hidden field on the form of Now()
So then I need an expression that looks at the difference between the updated time and the now time
i.e. I update a call on 29/7/2006 at 10:00 am
Its now 30/7/2006 at 10:01
i.e 24hrs and 1 minute later
As its more than 24hrs this flags and output to a filed e.g. 1
I then total all the "1" and therefore I have a ticket count of how many calls have not been updated in 24hrs.
So Im assuming I will need an after update event on my comments field put today's date in this hidden field ,and then maybe some sort of code which is maybe on my switchboard which looks at the difference of these dates and if greater than (or equal too) 24hrs it will output a value of 1.
And If I can do this I can also have a flag if its more than 2 days , then clear the 24hr flag an increment a 24hr flag etc.
My tickets should all be closed with 24 hrs , so this is something to capture anything that is open and hasn't been updated in 24hrs
Id appreciate some guidance on the best way to do this ?
Cheers
Jimmy
I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?
View 4 Replies View RelatedWould anybody know how to insert a status bar on a form and control it.Need help on this.Thanks in adv.
View 1 Replies View RelatedI've been avoiding this project but it has come up again. I have a file given to me each month. It contains a member number and status. The status could be any of the reason codes the user comes up with. So far there are ten. I imported the file into an access table. The users need a report summarizing the status changes if any. If there are any changes; the reason code will have a X under the reason code which pertains to them. I ran the unmatched query; but that gives me if the person was added or deleted. How do I search the reason code fields to check if there have been changes. If I ask for the nulls or is not null for each field; How would I know if it was null or not null in the beginning. I need a way to check to see if for example: Smith did not have a X under reason code 1 for the month of June but does in the month of July. I hope I didn't confuse anyone. Please help
Thanks