I have a "date" field and a "Expire dare" field, what I need is automatically obtain a expire date wich will be the last day on the month of the date field plus 1 year.
example
date = 15/02/2006
expire data 28/02/2007
How can I obtain the result?
I am trying to create some sort of warning to show that a date in one of my forms will expire in say 30 days. I need the warning to be a pop up (or similar) rather than be sent via email (only because I don't have Outlook set up and probably will not be able to). Effectively, I have a list of names and a column in my table to show an expiry date. To avoid missing the expiry date, I would like to be advised of this at least 30 days before hand.
View 3 Replies View RelatedGuys,
I am trying to put together a query that will give me a calculation of days between the currect date and a date in the future.
How do I tell a query that I want to display the current date, and then calculate this?
Is there a way that a DB expire after 30 days? :confused:
I have found some sample DB (see attachment) but it is limited only in a single machine, cause I'm going to distribute my DB on a Network. Can anyone help me how to incorporate this to my DB. Just re-post the the updated DB thanks.
Hi
I am creating a database where i store information on all computer and printers we have. The printers have warranties usually lasting 2/3 years
Is there a way i can set a function where when a printer warranty is expiring within 30 days a reminder is displayed on the main menu
Many thanks in advance
Kabir
I would like to create an Expire Indicator field. This field would automatically check against today's date and if the Expiration_Date equals to TODAY, the indicator would automatically be set to YES.
Does anyone know how to create this field in the table?
Thanks in advance!
Any way to make the query expired after a defined period but it must remain with that query only not for other queries. Also the query expires should have an option to be alive again or redated for next period after updation means expiry means stop working but not corruption or anything else.
View 5 Replies View RelatedHi
I have a table which keeps a record of days off sick.
The table has the field [First Day] = the first day they were off sick, in a dd/mm/yy format
and a [Last Day] = last day they were off sick, again in the same date format.
I need to have a query that will produce the actual day, and all of the days in between; so - if the First Day is 25/10/06, it will produce "Wednesday"
and then the last day is 27/10/06, it will produce "Friday"
But, I also need it to produce "Thursday" (which would be the 26/10/06)
Any ideas??
Thanks
Maria
I have 2 comboboxes to select a product no, and a unit of measurement
I have a materialprices table with unique records, keyed on productno and unitno, so i should get either one or no records returned from the following SQL
However, if i compare the values of the returned records with the search values and the tests, it shows a difference, although the difference is then showing as zero when I evaluate it.
(I found this because initially I had written the code using seek, and was trying to ensure I had located the correct record)
I have actually got round this by testing the abs difference as being greater than a minuscule value.
ie
If abs(rs!pmpprodno - cboProd)>0.01 Or abs(rs!pmpunit - cboUnit)>0.01 which does not produce an error
I could understand this if I was testing real numbers, but these are all integers - any ideas anyone?
so ignoring the dims - ignore any typos - the code compiles and executes properly - its just the equality test thats the problem
function lookupmat as boolean
strsql = "select * from tblmatprices where [pmpprodno] = " & cboProd & _
" and [pmpunit] = " & cboUnit
Set rs = CurrentDb.OpenRecordset(strsql)
If rs.eof Then
lookupmat = False
rs.Close
GoTo exitproc
End If
'having used the above where clause to find an item, this test now seems to produce a difference, even though the figures are the same!
If rs!pmpprodno <> cboProd Or rs!pmpunit <> cboUnit Then MsgBox ("Unexpected - There is a difference " & vbCrLf & _
"cboProd = " & Format(cboProd, "###.0000000000") & _
" Lookup = " & Format(rs!pmpprodno, "###.0000000000") & vbCrLf & _
"cboUnit = " & Format(cboUnit, "###.0000000000") & _
" Lookup = " & Format(rs!pmpunit, "###.0000000000") & vbCrLf)
'and now both of these tests are showing a difference! of zero
If cboProd <> rs!pmpprodno Then MsgBox ("Prod Was different " & cboProd - rs!pmpprodno)
If cboUnit <> rs!pmpunit Then MsgBox ("Unit Was different " & cboUnit - rs!pmpunit)
lookupmat = False
rs.Close
GoTo exitproc
End If
Hi everyone,
I really need help with transforming this table/data:
Project Date(dd/mm/yy) Value
----------------------------------
A...........1/1/2007...............100
B...........2/2/2007...............200
C...........3/2/2007...............300
D...........4/5/2007...............400
E...........5/5/2007...............500
F...........6/5/2007...............600
G...........7/6/2007...............700
into the following data format (13 columns in total, with 7 rows):
Project Jan Feb Mar Apr May Jun Jul ... Dec
-------------------------------------------------------------------
A............100.................................. ..........................................
B.....................200......................... ..........................................
C.....................300......................... ...........................................
D...............................................40 0.........................................
E...............................................50 0.........................................
F...............................................60 0.........................................
G................................................. .........700...............................
Assuming today is January 1st 2007, the 2nd column must starts with current month.
If someone can post me the SQL, or give me some tips or direct me to an article on how to achieve the results above..it'll be most appreciated.
Thanks in advance..
Hi, I am looking for help with a database I am trying to set up for a dog
club. I have created databases before although not for more than 6 years
and am now probably at the bottom end of Intermediate in terms of skill and
have no experience of using code builder as yet.
I need to create a database of dogs, their parents, owners and breeders and a few other bits of information such that I can print a catalogue of dogs entered at dog shows (see below) and am finding it very difficult.
Owner(s), address,
name of dog, sex (Dog or Bitch), Date of Birth, breeder, Sire - Dam
I've created various tables: Dogs, Owners, breeders, address, Dogs/Owners
(junction table) and Query to do the pedigree of the Dog (ie the parents)
which is a self join.
An owner can have more than one dog and a dog can have between 1 and 5
owners at the same time. Where there is more than one owner then the
address is that of the first named.
I also need to be able to add and subtract both dogs and owners to/from
their respective tables although subtraction will occur less frequently. I
then need to be able to produce catalogues with the details laid out as
above. Not all of the dogs or owners will be in each catalogue. I
anticipate doing this using a query and then using mailmerge in Word.
I appreciate that this may be too big a task to help me with but do not like
to give up until I have explored all avenues. If you can help then
I can let you know the field and joins that I have and other important
details.
Thanks (hopefully)
Pete
I've got a small employee training tracking database - no problem there. What the users currently do is to run a report from the database that shows who received what training, and when. They then open Powerpoint, pull up a training certificate document and manually type the employee name (by referring to the MS Access report), course taken and date the course was completed.
Here's my question: Can Access automatically pull up or go into Powerpoint and produce these Powerpoint certificates automatically? If so, how would I go about doing this? The users would love me forever (or at least until the next request) if this can be automated.
Yhank you in advance - it's amazing how knowledgeable you folks are!!
Hello, hoping for some help on the following scenario:
I need to produce a query which results in one record for each part number.
In a sample table of purchasing history (tblPODetails):
Fields:
PartNumber, Description, Cost, DatePurchased
We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.
I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.
The resulting description field would of course be the one in use for the latest received date.
I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...
I'm trying to print a report that displays only records from a particular site.
This is the code I've used:
Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click
Dim stDocName, strSiteName, stLinkCriteria As String
stDocName = "rptExtChkSht"
strSiteName = Me!cmbSiteName
stLinkCriteria = "[SiteName]=" & "'" & strSiteName & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_cmdReport_Click:
Exit Sub
Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click
End Sub
I'm wondering whether it won't produce only these records because of the design of my report (attached), with the Site Name under the SiteID Header...?? I wouldn't have thought it would matter, but...
Any help greatly appreciated.
My aim is to produce a report - using a drop down list (of customers from customer table) so that users can look at customer history.
I have created a form with a drop down list of customers - from customers table. I have used this to filter the results for a query on the orders table (gives me a history of that customer). All this works perfectly. I get all the transactions for that customer.
My question - is there a way I can create the same result but in a report format instead of the datasheet format. I.e the user will be given the data in report format when they select the customer from the drop down list.
I have produced a query, which counts the number of records fulfilling each set of criteria, but I can't now convert that into the matrix presentation.
I have a attached a copy of what is produced currently through Excel.
Basically each count on the query represents one of the boxes within the matrix. For example if the record Impact is 1, and the Likelihood is 1, then it would be counted within the bottom left hand box.
What I would like to know is this: I have produced a basic members table containing individual's details etc. How do I now produce an individual report for selected members (single page per member), in which I can determine which of the table details are included?
View 2 Replies View RelatedI have a database where we regulary import excel data to generated from a form sent to our clients. The excel data that we import normally holds around 10 to 40 records at a time.
The current process I use to do this is to run a macro that creates a new table (tblImportForm), and then run an append query to append those records to our existing main table (tblJobDetails). I should also mention that the macro first deletes the existing tblImportForm before creating a new one with the same name with the new data.I have used this method rather than appending the data straight into the existing tblJobDetails as I found I came into more problems with generating IDs etc.
What I want to do is have a report come up after the data has been imported/appended, that lists the new data imported with the new record IDs generated in the main table tblJobDetails. It would be easy to do this is if I only needed the data or could use the IDs in the first table I import to, but the idea of the report is to give the user the new IDs (PrimeKeys) from the second table that the data is appended to.I could maybe do a count of records in tblImportForm and then produce a report using a query from tblJobDetails that pulls that number (the count) of data from the last record backwards? But I don't know exactly how to do this.
Hi
im trying to combine the
Forename Column and Surname column so i can produce another column which is initial surname eg
Matthew | Williamson | M Williams
Kevin | smith | K Smith
how do i do this ive completely gone blank!!!!!! :(((
We have a need to produce hex and ascii in the same results field.
The field with data will be bound to a 2D barcode (the square ones you see everywhere now). The format of the data is to look like this:
[)><RS>06<GS>xyz<RS><EOT>
The <RS>, <GS>, and <EOT> all need to be hex values as follows:
<RS> = Hex 1E, Decimal 30
<GS> = Hex 1D, Decimal 29
<EOT> = Hex 04, Decimal 04
When I run my Access query as follows:
BarCode: "[)>"&Hex(30)&"06"&Hex(29)&"xyz"&Hex(30)&""&Hex(04)& ""
It produces the following:
[)>1E061Dxyz1E04
So, it just converts it to ASCII; however, when I go to scan it, it actually reads that information also. I need the 1E, 1D, and 04 to actually be scanned as HEX for the validation to occur for our client.
Is it possible using an Access query to return these desired results? If not, I will have to look atother piece of software to produce the bar code labels.
I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.
I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.
I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.
In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.
Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmPO_Received"
stLinkCriteria = "[ItemID]=" & Me.ItemID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".
I have a database (accdb) out of which I want to generate accdr files of which user can only access / view the reports section ONLY. Looking for VB code which will generate employee wise (or any criteria) accdr files?????
View 2 Replies View RelatedI would like to build a query that has a calculated field, involving value from a previous record. I have five columns, Year (sorted ascending), InvestmentRate, StartingBalance, AnnualContribution, and InterestIncome.
The first row, being the smallest year value, uses a previously defined elsewhere starting balance, annual contribution, and interest income. This is recognized via an Iif statement and establishing the first set of values which the second row should use to calculate.
The second record, and until the end of the query, automatically calculates the new starting balance value based on the sum previous row record's starting balance, annual contribution, and interest income.
Furthermore, for the second record, and untill the end of the query, the InterestIncome is calculated value incorporating the second row's StartingBalance value (which is the product of the sum of the previous record's values).
This calculated referencing to the previous record is causing difficulties.
Per the instructions detailed here...
http://www.fontstuff.com/access/acctut08.htm
...I created a parameter query in the form of a drop down box that, once an
option is selected, should display a form with a number of fields pre-filled
from a record chosen by the drop-down selection.
The drop down has a command button with the following code in the Onclick
event:
Private Sub cmdCreateReport_Click()
DoCmd.OpenQuery "qryUIRFollowUp", acViewNormal, acEdit
DoCmd.Close acForm, "frmOpenUIRLookUp"
As it now stands when I click the button I get the form but none of the
selected data is filled in. The fields are blank.
What code to I need to add to the above to make the form hold the data
selected from the drop down?
Thanks,
David
I'm trying to produce a report which uses a static deisgn and does not change. For example,I require in the detail of the report a table say 3 * 8 ....and in the report I need the data to fill each one of these cells. However I may not have 8 records , therefore I need the remaing columns/cells empty - the design of 3 * 8 must not change... at the moment I have a dynamic design so if i have 3 records I have 3 *3 table leaving a massive gap on the report.
View 2 Replies View Related