Search results

  1. A

    Less than or equal to

    Thank you. When I did this in a new query (using the same tables) I got a reserved error (-3087)?
  2. A

    Less than or equal to

    Just one other problem that I seem to have When I use a new query to try and Sum this field, I get a Data Type Mismatch error MinUsedOnContract: IIf((DateValue([OpenedTime])>=DateValue([ContractStart])) And (DateValue([OpenedTime])<=DateValue([ContractEnd])),[TimeUsed],0) The underlying...
  3. A

    Less than or equal to

    Thank you very much, that's really helpful!
  4. A

    Less than or equal to

    Dear Forum, I have the following fields in an access query and I expected the calculated field HoursUsedOnContract to display the value of 2.75, but it is giving 0 instead. Please can you advise what I could be doing wrong? OpenedTime = 31/08/2018 15:28:53 ContractStart = 01/09/2017 ContractEnd...
  5. A

    Primary Keys Gone

    ...I neglected to answer...a project will only have one client thank you for your help best regards
  6. A

    Primary Keys Gone

    Thank you My (limited) understanding is that if I want to relate a repeating field, then I need to use a one to many compound key. Am I wrong in that understanding? I set the Advisor, Week, TimeLog in that cascaded related fashion to help accommodate the 'form within a form within a form'...
  7. A

    Primary Keys Gone

    Thanks I have attached screen grabs of part of the database The field where two advisors seem to create duplicates of the auto number was TimeLogID It was when I tried to compact and repair that I got an error message, this prompted me to look at the design and that is when I saw that the...
  8. A

    Primary Keys Gone

    Dear Forum I am running an Access database that works like a timesheet system. It is split in to a front end and back end and the front end is used by about 15 users who each have a copy of the front end on their PC. The back end sits on the server. One of the users had an issue with their...
  9. A

    Table Structure

    Thank you, For the user, moving forward through forms, showing upcoming weeks is to provide a simple structure for the users I am trying to mirror an existing accounting timesheet system that shows future dates and the user writes against those weeks/days I am thinking in terms of forms...
  10. A

    Table Structure

    Thank you very much for pointing out the importance of getting the design right. I have tried to attach a design. I would like the user to select their 'Advisor' name (at the top of a form), then, within that form, for another form to display week number or week commencing, and then within that...
  11. A

    Table Structure

    Thank you very much My vision for my form layout is: Main form: Advisor Subform: Week Subsubform in table layout: WeeksTimeLogs and so I am struggling to consider a table layout other than 3 tables connected one to many Any ideas? thanks for your help Alex
  12. A

    Table Structure

    Dear Forum I am creating a simple timesheet database (see attached form layout) And wanted help to get the table design right. i was thinking this would require 3 tables AdvisorName Week TimeSheet I was thinking that AdvisorName would link to Week with 1-M and that week would have a compound...
  13. A

    Query to give value based on most recent date

    thank you arnelgp There may be more than one TimeForecast value on one day At the moment, the final query can display multiple values for one day and I only really require one value. I am therefore using a group function for ProjectID and Max of TimeForecast, as that will give me the...
  14. A

    Query to give value based on most recent date

    thanks very much for your help plog
  15. A

    Query to give value based on most recent date

    Dear Forum, I have a table that is part of a wider database. The table includes the following fields: AdvisorName * (Text) TimeLogID* (auto number) ProjectID (number) LogDate (date field) TimeForecast (number field) The compound primary key field is indicated with a *. The other fields...
  16. A

    Run-time error 2147217900

    Thank you for your help Stupid me did not paste whole of module code in, here it is again Option Compare Database Option Explicit Public Function Conc(Fieldx, Identity, Value, Source) As Variant Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim SQL As String Dim vFld As Variant Set...
  17. A

    Run-time error 2147217900

    Hello, I have a concatenate query that is run using a module given further below, which on its own works OK When I relate the concatenate query to a related table in a new query or add additional fields to it, it runs OK for a short while, but when I scroll up and down through the data, I then...
  18. A

    Week Number and Date Part

    Thank you All arnelgp. I managed to get your suggested public function in a module to work, however the first day of the week appears to be Friday, whereas I need it to be Mondays. For example I need Monday 06/06/2016 through to Friday 10/06/2016 to be in week 23, whereas Friday 10/06/2016 is...
  19. A

    Week Number and Date Part

    Hello, I have a simple sales database in Access 2013 with various date fields I need to produce queries and reports using week numbers When I use Format([DateField],"ww") Access gives me week numbers that are 1 week ahead of my business calendar week numbers, and they don't match up. I...
  20. A

    And Criteria in Query

    Hello This is probably a silly question. I have a simple database with a one to many relationship between 'Consumer' and 'Products'. One consumer has many products. Some consumers consume chocolate OR some consume cola, and some chocolate AND cola I want to run a query that gives me only the...
Top Bottom