Stuck With Dates
Jan 27, 2008
Hello there,
I'm making a database for a friend who works for a small magazine.
I'm trying to write a simple query so that she can product a list of all the people who the magazine should be sent to.
Each customer has an 'end issue date' (that is the date at which their subscription expires).
So, in the query wizard, for the field 'EndIssue' I've put the following in 'Criteria'.
<=[Enter the issue date]
If I run the query with the date '01/02/2008' I come up with 170 matches. However, if I run it with the date '01/01/2008', I come up with zero.
Damn!
Does Access handle dates in some funny way?
I have a simalar problem with checkboxes?
I'd be really grateful to anyone who could offer some help!
Cheers,
Rob
View Replies
ADVERTISEMENT
Aug 6, 2006
i have been trying to for the last several weeks create a database which holds information about companys e.g addresses etc on it. It also has a subform on it which runs a query and shows me the courses they run with a button beside it. This subform propertys is set to continues and so there is a button that appears beside each course. How can i get it so when i click on the botton beside that particualr course it will load up another form and display some more information holding the times and dates of this course.
View 11 Replies
View Related
Jun 15, 2007
I've been designing a db and entering data in the process. Now I'm almost done with this project and other people are interested in this db. Is there a way to empty all table records? Is there a command to have a blank db?
My db has almost 5,000 records :S and each record has another table with 5 linked records
so there are like 25,000 records to delete. I know there is a way and its to create another table with the same fields and importing the rest of the objects... but I want to make there is no other way.
Another Q is how to add security to my db how to "attach" the db to a hardware of a computer so if the db is installed on another computer it asks for a serial number...I have asked this before but no one had answer me this.
Thanks in advanced
View 7 Replies
View Related
Jan 8, 2008
Hi
I want to use MS access to build a diary / appointment system, and a table for storing contact details.
I am struggling with the appointment system. I was going to try a table with the date and diffefent app times as fields. then put it into a form so that it looked like a diary page and i could just add names to the times., this would have ollowed be to scroll backwards and forwards through the dates with ease (as each day would be a new record)
The problem i have found is that this makes running quires on individuals difficult,i would like to see for example how many appointments a person has missed, the dates of there next appointments if they have pre booked ect.
any suggestions as how i should go about this?
View 6 Replies
View Related
Apr 28, 2006
I'm trying to update a table based on a field in an unbound form
Here’s the code:
Private Sub Command38_Click()
Dim strPartNo As String
strPartNo = Me!fldPartNumber
Set dbs = CurrentDb()
Set rstBalUD = dbs.OpenRecordset("SELECT * from PartsInventory WHERE CO_PART_NO = [fldPartNumber]", 1)
rsBalud!SERVBAL = Me!fldNewServBal
rstBalUD.UPDATE
End Sub
Here’s the error:
Run-time error ‘3011’
The Mircosoft jet database engine could not find the object ‘SELECT *
from Partsinventory WHERE CO_PART_NO = [fldPartNumber]’.
Make sure the object exists and that you spell its name and path name
correctly.
Reply With Quote
View 2 Replies
View Related
Oct 7, 2007
I have a table that has the follwoing fields:
-student name
-pesent percentage
-compare value 1 (a number)
-compare value 2(a number)
now i want a query to display the following fields as an output
-Student name
-Compare value
-present percentage 1
-present percentage 2
so where "compare values" 1 and 2 are equal it will display 1 record in the query that will show both the values. in respective fields.
View 4 Replies
View Related
Mar 4, 2005
I have a form which includes two fields (amonst others) Town and STDCodes.
What I want to do is enter the Town from a drop down list and the STDCode is automaticly inserted in the STDCode field, the towns are all local about 20 in total. How can I do this please in simple easy steps.
Malcolm
View 2 Replies
View Related
Mar 12, 2005
Hi all.
I'm creating a database for a work experience database for a school where each pupil has to be assigned to a company. The problem is that on the form "Final form" where it says Work ID I want it so that each pupil is assigned to that company and then I can create a reports.
For example I choose work ID 2 for one of the pupils and when I create a report it'll have the information of the student profiles with the companies they're supposed to go to.
Can anyone give me a hint or any guidance on how I can do that?
Thanks.
I haven't used Access for that long so I'm useless with all the technical terms, sorry.
View 1 Replies
View Related
May 11, 2005
Right im really stuck on this, how do you get the date visit to update correctly, it requeries but some times shows data it shouldnt. it should corispond with the subform.
next thing is the text box that says 'name needs to display the notes from that visit in , ( that thats in the sub form far right box.
any help on this would be much appreciated
in the dtabase its form "CD2"
View 10 Replies
View Related
Sep 20, 2004
Ok so I have been working on this problem for two days, and I am stuck.
It's been a little while since I've used Acess, so please pardon this question.
A database has been built compiling thousands of records of information for shipments. Here is the main problem.
Imagine if you will, that a truck is sent from teh warehouse to a customer, with the customer's order(s). So in the
database, is a record for each order. The order is comprised of the Bill of lading for the truck, the customer's name, and information about that specific set of palettes. So for example a truck may be sent to a customer, and there are 3 types of products on the truck, or only 1 type. For each record, we have the cost of shipping that individual product, as well as the amount paid for the entire bill of lading.
We've creaetd a query to pull out all the trucks that were shipped with just one product. We've also created a query to pull the 'duplicate' records. The problem lies in manipulating this "duplicate" query.
Each record in this query has multiple entries for bill of lading field, the customer's name, amt paid, and cost amt. We want to create a query from the "duplicate query", that will return just ONE listing of each bill of lading, as a well as a total cost of shipping. so for example if
bill 704555, had 3 shipments on it at a cost of $50, $17, and $33, we want a query that will compile these 3 listings into one listing (So now Bill of Lading appears once, with all the requisite information, and the total cost of $100 in a new field in this query, called totalCost). We'd calculate this by hand, but there are approximately 7000 recodrs in the query, and this will continue on an ongoing basis, so the query needs to perform teh summations.
thanks in advance for your help!
Matt
View 1 Replies
View Related
Jan 10, 2008
Hello! I'm developing a database that is to be used for case management. Being completely self-taught it's been a bit of a struggle!!
I have a form where data is entered regarding the details of each case. This form has a button on it that opens another form for entering the details of the participants involved in each case. To make the database as user-friendly as possible I would like to add a field to my main form which counts the number of participants. The forms are linked by a case reference number & I made a query (that works) that counts each instance of a particular case number in the participants details form....then I got stuck. Is there a way of adding the results of a query to a form that is based on a table? Or else is there a way I can perform that calculation in the main form?
I would really appreciate it if you could make your replies as "Access for Dummies" as possible!
Thanks!
Oh it's Access 2007 if that helps at all.
View 5 Replies
View Related
Mar 1, 2006
I am trying to create a bowling league db in access 2003. I believe I have started with the right structure but I am having some trouble with recording the scores of the bowlers. Attached in a word doc is how I have set up the relationships. Any thoughts would be greatly appreciated.
D.J.
View 2 Replies
View Related
Dec 15, 2006
I am having difficulties with combo boxes for some reason and cant get it to work. I have spent this week trying to use examples found here and other places but still not successful.
Have looked at the examples on font stuff and have downloaded various examples here on these forums.
If anyone can help set this one up for me, then I can use it for a model for the rest, just not sure how to setup initially. Even if you get me closer to what I am now.
I thought I came close yesterday, but the combo boxes didnt change the records in the database after choosen.
-----------------------------------
I need the classification drop down box on the member details form to prompt the secondary box choices. Initally if someone chooses "Lost Opportunity" in the classification then the reasons why need to be available in the second box. When choosen they need to be saved in the database. I have tables setup for classification (tblClassification) and for reasons (tblLostopportunityreasons). If anyone at all can help set this up I would soooooo appreciate it.
----------------------------------
Then I can use this to help me do others that I need to do. I am looking for this one xmas present since I am stuck and my understanding of Access is rather new.
Thanks.
View 7 Replies
View Related
Apr 18, 2007
This is my first time posting, so hopefully I get this right...
My question is this...I am currently working on a subform that pulls information from a main form through a main table in order to properly parce a set amount of time for technicians working on the same task. At the same time, I am also trying to figure out how to present this info in the subform without duplicating the first line.
I apologize for sounding so redundant. Please let me know if you need further clarifications.
Thanks
Joe
View 2 Replies
View Related
Jun 15, 2007
I've been back thru this DCount function, here in the forum and elsewhere. I have posted about this function before and even went back to my old post. Looks like I still need some help.
Here's the premise:
My database has a query that tracks Payments made to Students who are on the Federal Work-Study program. We have 4 categories of work: On-Campus; Off-Campus Community Service; Off-Campus Family Literacy; College Support Services.
Of all payments made to students in the year for Federal Work-Study, there are some payments in each category. On the Report, based on the query "FISAP Detail Query", programmed to show every disbursement(payment), I'd like a count of each type in the Report Footer .
I have a control on the report that I'd like to use to count the number of students paid for Community Service.
=DCount("[StudentID]","FISAP Detail Query","[Community Service Amount]>0")
and I've tried
=DCount("[StudentID]","FISAP Detail Query","[Community Service Amount]>'0'")
Count the number of students listed in the FISAP Detail Query who have a [Comunity Service Amount} greater than zero. Sum totals of disbursements for the year for each student are displayed in the Detail section of the report as a single record. So how many of these records have Commuity Service disbursements; that's what I'd like to know.
The formula returns #Error.
Anybody have any advice for fixing this? It must be some syntax or trying to use the wrong function to do the job.
Any help will be very much appreciated
Cheers!
Goh
View 8 Replies
View Related
Jan 6, 2008
Hello everyone,
I didn't see a section for DAPs so I'll try posting this here.
I have a very odd problem which occurs with my DAP. I have a DAP located on a network drive where it can be accessed by people who use VPN to access the drive. Most users can access the DAP just fine and have no problem loading the page and entering/receiving data. For some users however, the page gets stuck with IE saying "databinding" and it never can finish loading. It's odd because there are two identical computers with two identical connections, but one simply cannot load the DAP. What's even more odd is that the problematic user CAN open the DAP by entering the mdb file, and running the DAP from inside of access.
Both users are running Windows XP service pack 2 with IE 6. I have tried eliminating all security settings, accepting all cookies, allowing all controls, removing all IE add-ons, but nothing seems to work. This project is very important and if anyone can offer some advice I would be infinitely grateful.
View 3 Replies
View Related
Oct 6, 2006
Thanks for looking at my post. What I have done is this, i have built a custom search form for my access database. What i need is a textbox that shows how many records will show up if the user clicks generate form.. I want this textbox to update every time the person changes his search criteria. For example
the person wants to search by address, by typing in
Gold into the address textbox the recordcount textbox would show the user if he were to press generate form he would get
1200 records
now if he changes the address to
GoldStone -- the recordcount textbox would then update to show only
14 records would be shown if he/she generated the form...
Here is my current code
***********
Set db = CurrentDb
Set qdf = db.QueryDefs("MainDB_Search")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rsCount = rs.RecordCount
MsgBox (rsCount)
txtGenerateReport.Value = rsCount
************
Now the problem with this code is that it works the first time the user types in search criteria, however when he/she tries to change it from Gold to Goldstone -- nothing happens, it runs the code above and returns 1200 records instead of the correct 14...
Please help :)
Thanks
Jason
View 4 Replies
View Related
Oct 17, 2006
Hello all,
I am currently working on an sql query to find employees which handled more orders than average in a certain half year. For arguments sake the half year is august-dec1997. I will use two queries, one to calculate COUNT and one for the avg:
So far I have the COUNT query sorted:
SELECT Employees.EmployeeID, Count(Orders.EmployeeID) AS CountOfOrders
FROM Employees, Orders
WHERE (((Employees.EmployeeID)=[Orders].[EmployeeID]))
GROUP BY Employees.EmployeeID;
However, I am uncertain how to proceed from here and find the >AVG part of this query. Any help would be appreciated- thankyou :o
To add to this I think the next table should be like this:
SELECT qrycountOfOrders.EmployeeID, Employees.FirstName, Employees.LastName, qrycountOfOrders.CountOfOrders,
WHERE
FROM qrycountOfOrders, Employees
WHERE qrycountOfOrders.EmployeeID = Employees.EmployeeID;
But I am unsure where to put the >=AVG...
View 2 Replies
View Related
Jul 14, 2006
I have a form that i use to make labels.
I want the user to be able to specify in the form the numbers of labels to print.
After reading access for dummies and other manuals, google and calling my local IT guys, I've exhausted all examples I was given.
CAN/ WILL ANYONE HELP ME?
View 1 Replies
View Related
Oct 2, 2006
How do I write this, I do know the code needs to go on the before update of the form
If a quanity is entered then defect_descripion must contain something.
I've tried:
If quanity <=0 then
defect_descripion not null or defect_descripion =""
msgbox "you must select a defect descripion"
I have the this part of the code
me.defect descripion.setfocus
cancel = true
end if
end sub
please help I'm at my wits end
View 2 Replies
View Related
Jul 27, 2006
Hi all,
I'm stuck on IF statement. what I wanted in plain english is
If user name does not equal steven or george then quit.
what i've got currently looks like
----------
If Me.CurrentUserID <> "steven" or "george" Then
DoCmd.quit
End If
---------
but this doesn't work, any body got clues?
View 3 Replies
View Related
Jul 16, 2005
The problem: I have my database set to open a form on start-up. On opening that form I put in a command for a message box. When I click OK on the message box it tries to open the form again which leads to the same message box. I need to somehow get to the design view on start-up. Any help would be appreciated. Thanks Bob
View 1 Replies
View Related
Oct 13, 2005
Hi Guys,
Im using the sendobject macro to email a report when the database is opened, yet when it does, the email is only in my outbox, doesnt seem to be sent to other users!!! Can anyone tell me what is going on or how to get around this??
Thanks in advance.
View 4 Replies
View Related
Jan 13, 2006
:mad:
I really need to find a way to run a bit of code which exports a Access query to excel and automatically saves the workbook.
However it is not quite so straightforward - I need the code to create different workbooks based on the contents of a field within the query which will be duplicated (it is actually a school name and several hundred pupils will be attached to a school) and use this field (schoolname) as the excel filename.
It needs to be at a click of button - the code runs creates loads of different workbooks and then says done!!!
Please Please Help Me.
View 2 Replies
View Related
May 10, 2007
Hi, all!!
I need to generate 1 email with 3 attachments from an Access Db. These attachments are canned reports that are generated each week with fresh data.
I've done several searches and found a lot of good information here. Based on what I've read, I decided to output the 3 reports to a folder in My Documents and then automate Outlook to send the message.
I've used the output function to create the 3 files. No Problem, works well.
Then I found this code for automating Outlook. (Pasted below)
I can get it to work (following either step 7 or step 8 below) but only if I include the attachment path in the SendMessage command.
Assuming the full paths are:
C:My DocumentsReport1.snp
C:My DocumentsReport2.snp
C:My DocumentsReport3.snp
how do I modify the code to automatically attach all 3 files?
Any ideas?
As always, thanks for taking the time to help,
BeckieO
Sub SendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Henny Penny")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Lucky Ducky")
objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test - I promise." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
7. To test this procedure, type the following line in the Immediate window, and then press ENTER: SendMessage "C:My DocumentsCustomers.txt"
8. To send the message without specifying an attachment, omit the argument when calling the procedure, as follows:SendMessage
View 3 Replies
View Related
Mar 2, 2006
Just cant seem to find anything on this although i'm sure there is?
I've got a query and with one table in it or the other the records show up fine, but as soon as i include the second table the results come up blank.
What am i doing wrong?
Just cant seem to think?
Thank you in advance
View 3 Replies
View Related
