27. februar 2010 - 21:40Der er
18 kommentarer og 1 løsning
Betinget udfyldning af et felt.
Jeg er i gang med at laere Access. Jeg sidder nu fast i nedenstaaende problem. Jeg haaber paa hjaelp til at komme videre.
Jeg skal lave en omkostningsoversigt maaned for maaned for medarbejdere der er ansat de paagaeldende maaneder. Jeg har en tabel med medarbejdere med navn, startdato, slutdato, og maanedlig omkostning. For eksempel:
Det vil sige (for at skaere det ud i pap) at januar skal indeholde de 5000 for Hansen og ikke andet, february 5000 for Hansen og 4000 for Jensen, december kun 4000 for Jensen, o.s.v.
Jeg har lavet en formular med tabellen som kilde. Formularen skal have 12 ekstra felter, en for hver maaned, for de omkostninger der hoerer til hver maaned. Foreloebig har jeg kun lavet feltet for januar.
Jeg tester om omkostningen for en medarbejder skal i januar feltet ved at undersoege om 1/1/2010 ligger mellem startdato og slutdato.
Jeg har proevet paa to maader (plus paa alle mulige andre maader som jeg ikke skal beskrive her).
(1) Jeg har i VBA lavet en sub for formularen saaledes:
Private Sub Form_Current() Dim beloeb As Integer Dim startdato As Date Dim slutdato As Date Dim post As Integer beloeb = Me.beloeb.Value startdato = Me.startdato.Value slutdato = Me.slutdato.Value post = beloeb If #1/1/2010# <= startdato And #6/1/2010# >= slutdato Then Me.post.Value = post End If End Sub
Men saa faar jeg 5000 for alle medarbejdere. Jeg fortolker det saaledes at koden for det foerste ignorerer betingelsen (datoen mellem start og slut) og for det andet kun tager vaerdien for den foerste rekord.
(2) I expression builder for feltet januar indfoerer jeg denne expression:
IIf(#1/1/2010# between [startdato] and [slutdato] , [beloeb] , "")
Men jeg faar en meddelelse at det er en ugyldig vaerdi.
For de som er klogere end mig paa Access (hvilket ikke vil sige ret meget,) kan i give mig et skub i den rigtige retning.
Hi Christian Heres another approach wher you do it all in a query. The example if for two monthes (Jan + Feb)
SELECT tblDates.Navn, tblDates.StartDato, tblDates.SlutDato, tblDates.Beloeb, IIf(1>=Month([StartDato]) And 1<=Month([SlutDato]),[Beloeb],0) AS Jan, IIf(2>=Month([StartDato]) And 2<=Month([SlutDato]),[Beloeb],0) AS Feb FROM tblDates;
terry, you gave me a workable solution to the immediate problem. I had simplified the example too much, the start- and end-dates spread over several years so IIf(1>=Month(StartDato)... is not sufficient, but it works with IIf(#1/1/2010# >= StartDato AND #1/1/2010 <= slutdato#, Beloeb, 0). Make a 'svar' for points.
You did not respond to my secondary concerns, why I could not create a workable expression in a form or a workable VBA code. I intend to make this subject to one or two separate questions. I hope for your (and others') input then.
mugs, jeg er ikke helt klar over hvor semikolonerne skal komme, men jeg foreslaar at slutte dette spoergsmaal her. Saa kan vi maaske tale semikolonner naar jeg (sandsynligvis i morgen) laver nye spoergsmaal (hvis du til den tid stadig har tid og interesse.)
I'm not sure I undertsand your code and why its in the on Current event.
Can you give an example of the data we spreading over several years? You say your question that you need 12 fields on the form, one for each month, but that cant be right if the interval is over a number of years, you would need columns for every year/month in the interval!
Hvis der er en dato for budgetafløbet, må det være muligt at anvende funktionen DatePart i en forespørgsel for at konkretisere hvilken måned og hvilket år der er tale om.
terry, no it is the start- and end-dates that can be spread over several years. If an employee's startdate is 1/2/2009 and we make a report for 2010 then the January expenses must be shown. Therefore it does not work to test whether 1>=Month([StartDato]).
mugs, ja det kan uden tvivl goeres smartere, og jeg vil gerne kunne lave det saaledes at man kan vaelge for hvilken periode rapporten skal laves, men foreloebig er jeg tilfreds med at kunne krybe, og saa kommer jeg, 'in due course' til at kunne loebe.
I just clicked the link. It does not work because it ends in a full stop, but with that removed I could download it right away. Do you not use Access 2007? Should I send it as an email attachment? (In this case I need an email adres.)
It works very well, thanks to your input. So my immediate problem has been solved. At the same time I got more insight. For example I used for the first time IIf constructions in a query. I am asking nothing more in connection with THIS thread, except for a 'svar' so that I can give points and close the thread. If you had understood otherwise I am sorry.
However, there is a lot more I need to learn more about such as writing expressions and VBA code. So I shall in due course (probably one or two days) try to accomplish the same task by creating a form and fill the months in by the help of expressions for the source and by the help of a VBA module. I shall no doubt fail. My plan is then to raise new questions where I state the objective and show my efforts and hope for equally enlightened help. That is when I get so far.
My favourite Danish poem, Piet Hein, wrote short poems called "Gruks." The shortest of them all goes like this, in Danish: "Gro i ro. Laer af traer." In English (but then the rime is lost:) "Grow quietly. Learn from trees."
In English there is an idiom "You cant's see the forest for the trees" which I think was the reason that I wasnt aware you'd found what you were after.
Anyway its nice that your making use of those trees.
I suspect you've worked with databases previously?
Worked with databases previously - not really. The history is that I was borne before even the first experimental computers were built and I have missed the 'computer generation' so that computers will never come as natural to me as for example to my grandchildren who have been around them since they could crawl. But some years ago I started, for fun, to study computer science on the Dutch Open University, and there I now have a batchelor degree. I have therefore had to learn quite a lot of theory, inclusive theory on data structures, normalisation, etc. But as for practical experience, almost none. Since I got pensioned I have worked as volunteer for some organisations and there got a bit of practice. Presently I work one to two days a week in an organisation in Antwerpen (Belgium) where they use Access to administer their volunteers and those who visit the organisation. That is the reason I am now trying to learn Access. Furthermore I practice by answering questions on Eksperten. The questions in this thread actually relate to a question I am trying to answer.
A batchelor degree in computer science, thats more than what I have. I'm "self tought" and have been working with IT for around 25 years now. Most og my learning has been "Hands on" and forums such as eksperten which is a great resource of information.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
