Im fairly new to access and would prefer not to use any scripting as such, but Im trying to create aselect query based on two tables: tblProduction, from which the query uses DeptID, ProductionQty, and ProductionDate fields; - and tblDepartment table, from which ive used the Department field to simply link a department name to the corresponding DeptID.
the criteria are made up of user prompts, which basically allow the user to input a dept ID, a start date and an end date. the query should then show on a report (which I have already made) what the production output is for the specified department between the specified dates. for this I have used the prompts:
"[Enter Department ID:]" for the DeptID field
">[Start Date:] AND <[End Date:]" on the first line of the ProductionDate field, with two separate OR arguments: "=[Start Date:]" and "=[End Date:]"
I have also included the [Start Date:] and [End Date:] to be displayed when the query is run.
When I first executed the query, it worked, but now it will only display data if i use the exact same input as the first time I tested it. If I enter a different department, or different dates, the query will display no results at all.
Have I overlooked something in the relationships, or something else?
If anyone could help, I would be most appreciative.
Cheers
Access 2002, WinXP Pro
6 1890 PEB 1,418
Expert 1GB
Hi
If your departement ID is a number than put as condition:
val([Enter Department ID:])
And for the date condition:
Between Cvdate([Start Date:]) AND Cvdate([End Date:])
Thanks. Tho the deptID isnt a number, its the first 3 letters of the department name. Should I change that to Str([Enter Department ID:]) ?
NeoPa 32,556
Expert Mod 16PB - WHERE (([DeptID]=[Enter Department ID:]) AND ([ProductionDate] Between [Start Date:] And [End Date:]))
You shouldn't need the CStr() or CDate() functions.
Still no luck, virtually the same SQL code as was already there. After retyping the entire query again as a new query, it still does the same thing. Only when I enter the dates and deptID's that I used the very first time i tested the query will any data be displayed in the results. Different dates or ID, nothing.
The data in the tables are still intact. Im wondering if ive set the primary keys wrong, heres how ive set the tables up: - tblDepartment - DeptID(PK); Department
- tblProduction - ProductionDate(PK); DeptID(PK); ProductionQty
a one-to-many relationship exists between the two DeptID fields. The query refers to the one in tblProduction.
Cheers for your help so far everyone. Much grateful
PEB 1,418
Expert 1GB
However try with
Between Cvdate([Start]) and Cvdate([End])
Because it is possible that your parameters for Dates are setted for text parameters... So you convert your dates in real dates...
Also If you have problems with CVdate check your regional Settings and the format of your Date
Enter the date after your format for a date...
Best is to use Ctrl & ; to introduce the current date if need it!
Still no luck, virtually the same SQL code as was already there. After retyping the entire query again as a new query, it still does the same thing. Only when I enter the dates and deptID's that I used the very first time i tested the query will any data be displayed in the results. Different dates or ID, nothing.
The data in the tables are still intact. Im wondering if ive set the primary keys wrong, heres how ive set the tables up:- tblDepartment - DeptID(PK); Department
- tblProduction - ProductionDate(PK); DeptID(PK); ProductionQty
a one-to-many relationship exists between the two DeptID fields. The query refers to the one in tblProduction.
Cheers for your help so far everyone. Much grateful
Leave DeptID in tblProduction as a Foreign key rather than make it part of the primary key. As ProductionDate is unlikely to be a unique key add another key set to autonumber and make it the sole primary key.
Having ProductionDate and DeptID as a joint primary key means only one record for any dept on any particular date can be entered.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: dogu |
last post by:
Noob alert.
Code is below.
File is saved as a .php.
What I'm trying to do:
User uses 'select' box drop down list to pick a value.
Value ($site) is derived from a db query. This works fine....
|
by: Cherrish Vaidiyan |
last post by:
hello,
Thanx for replying to my earlier Google group query about Replication
of Oracle 9i on RH9.IF the query followed is being repeated by me I am
sorry for it..
I want to implement...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON...
|
by: mo |
last post by:
I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalized. My inherited DB is not normalized and I find it
extremely irritating due to the workarounds needed.
I created...
|
by: John Baker |
last post by:
Hi:
As those who have looked at this newsgroup recently will realize, I am a neophyte with
Access, although I have experienced with Approach (the Lotus product). There are things I
could easily...
|
by: JMCN |
last post by:
hi
i have a general question regarding append queries in access 97. each
week i need to update my table(tblonlinereg) with new or modified
records. firstly, i import the text file into my...
|
by: Lyn |
last post by:
This one is difficult to explain, so I will cut it down to the basics.
I have a major table 'tblA' which has an autonum field 'ID-A' as primary key
(of no significance to users). 'tblA' contains...
|
by: Robert |
last post by:
I am an attorney in a non-profit organization and a self-taught programmer.
I'm trying to create a client db that will allow me to search for potential
conflicts of interest based either on Social...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |